Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

70-761 > Query Keywords > Flashcards

Query Keywords Flashcards

(77 cards)

Start Studying
1
Q

ADD

A

adds a column in an existing table
Example
ALTER TABLE Customers
ADD Email varchar(255)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

ADD CONSTRAINT

A

Adds a constraint after a table is already created
Example
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

ALTER

A

adds, deletes or modifies columns in a table,or changes the data type of a column in a table
Example
ALTER TABLE Customers
ADD Email varchar(255)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

ALTER COLUMN

A

changes the data type of a column in a table
Example
ALTER TABLE Employees
ALTER COLUMN BirthDate year;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ALTER TABLE

A

adds, deletes, or modifies columns in a table
Example
ALTER TABLE Customers
ADD Email varchar(255);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

ALL

A

returns true if all the subquery values meet the condition
Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

AND

A

only includes rows where both conditions are true
Example
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

ANY

A

returns true if any of the subquery values meet the condition
Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

AS

A

renames a column or table with an alias
Examples
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

ASC

A

sorts the result set in ascending order
Example
SELECT * FROM Customers
ORDER BY CustomerName ASC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

BACKUP DATABASE

A

creates a back up of an existing database
Example
BACKUP DATABASE testDB
TO DISK = ‘D:\backups\testDB.bak’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

CASE

A

creates different outputs based on conditions
Example
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN “The quantity is greater than 30”
WHEN Quantity = 30 THEN “The quantity is 30”
ELSE “The quantity is under 30”
END
FROM OrderDetails;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

CHECK

A

a constraint that limits the value that can be placed in a columns
Exmaple
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

COLUMN

A

changes the data type of a column or deletes a column in a table
Example
ALTER TABLE Employees
ALTER COLUMN BirthDate year;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

CREATE

A

Creates a database, index, view, table or procedure
Example
CREATE DATABASE testDB;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

CREATE DATABASE

A

creates a database
Example
CREATE DATABASE testDB;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

CREATE INDEX

A

creates an index on a table (allows duplicate values)
Example
CREATE INDEX idx_lastname
ON Persons (LastName);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

CREATE OR REPLACE VIEW

A 
updates a view
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

CREATE TABLE

A 
creates a new table in the database
Example
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

CREATE PROCEDURE

A 
create a stored procedure
Example
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

CREATE UNIQUE INDEX

A

creates a unique index on a table (no duplicate values)
Example
CREATE UNIQUE INDEX uidx_pid
ON Persons (PersonID);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

CREATE VIEW

A 
creates a view based on the result set of a SELECT statement
Exmaple
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

DATABASE

A

creates or deletes an SQL database
Example
CREATE DATABASE testDB;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

DEFAULT

A 
a constraint that provides a default value for a column
Example
CREATE TABLE Persons (
    City varchar(255) DEFAULT 'Sandnes'
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
DELETE
deletes rows from a table Example DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
26
DESC
sorts the result set in descending order Example SELECT * FROM Customers ORDER BY CustomerName DESC;
27
DISTINCT
selects on distinct(different) values in the result set Exmaple SELECT DISTINCT Country FROM Customers;
28
DROP
deletes a column, constraint,database, index, table or view Example ALTER TABLE Customers DROP COLUMN ContactName;
29
DROP COLUMN
deletes a column in a table Example ALTER TABLE Customers DROP COLUMN ContactName;
30
DROP CONSTRAINT
deletes a unique, primary key , foreign key or check constraint Example ALTER TABLE Persons DROP CONSTRAINT UC_Person;
31
DROP DATABASE
delete an existing SQL database Example DROP DATABASE testDB;
32
DROP DEFAULT
deletes a DEFAULT constraint Example ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;
33
DROP INDEX
deletes and index in a table Example ALTER TABLE table_name DROP INDEX index_name;
34
DROP TABLE
deletes an existing table in a database Example DROP TABLE Shippers;
35
DROP VIEW
deletes a view Example DROP VIEW [Brazil Customers];
36
EXEC
executes a stored procedure Example EXEC SelectAllCustomers;
37
EXISTS
Tests for the existence of any record in a subquery Example SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
38
FOREIGN KEY
``` A constraint that is a key used to link two tables together Example CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); ```
39
FROM
specifies which table to select or delete data from Example SELECT CustomerName, City FROM Customers;
40
FULL OUTER JOIN
returns all rows when there is a match in either left table or right table Example SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;
41
GROUP BY
``` groups the result set(used with aggregate functions: COUNT, MAX, MIN, SUM, AVG) Example SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; ```
42
HAVING
``` used instead of WHERE with aggregate functions Example SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; ```
43
IN
allows you to specify multiple values in a WHERE clause EXAMPLE SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
44
INDEX
creates or deletes an index in a table Example CREATE INDEX idx_lastname ON Persons (LastName);
45
INNER JOIN
returns rows that have matching values in both tables Example SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
46
INSERT INTO
inserts new rows into a table Example INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
47
INSERT INTO SELECT
copies data from one table into another table Example INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;
48
IS NULL
``` tests for empty values Example SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL; ```
49
IS NOT NULL
``` tests for non-empty values Example SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL; ```
50
JOIN
joins tables, Same as INNER JOIN (either can be used) Example SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
51
LEFT JOIN
returns all rows from the left table and the matching rows from the right table Example SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
52
LIKE
searches for a specified pattern in a column Example SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
53
LIMIT
specifies the number of records to return in the result set Example SELECT * FROM Customers LIMIT 3;
54
NOT
only includes rows where a condition is not true Example SELECT * FROM Customers WHERE NOT Country='Germany';
55
NOT NULL
``` a constraint that enforces a column to not accept NULL values Example CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int ); ```
56
OR
includes rows where either condition is true Example SELECT * FROM Customers WHERE City='Berlin' OR City='München';
57
ORDER BY
sorts the result set in ascending or descending order Example SELECT * FROM Customers ORDER BY CustomerName;
58
OUTER JOIN
returns all rows when there is a match in either left table or right table Example SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;
59
PRIMARY KEY
``` a constraint that uniquely identifies each record in a database table Example CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); ```
60
PROCEDURE
``` a stored procedure Example CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; ```
61
RIGHT JOIN
returns all rows from the right table and the matching rows from the left table Example SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
62
ROWNUM
specified the number of records to return in the result set Example SELECT * FROM Customers WHERE ROWNUM <= 3;
63
SELECT
selects data from the database Example SELECT CustomerName, City FROM Customers;
64
SELECT DISTINCT
Selects only distinct (different) values Example SELECT DISTINCT Country FROM Customers;
65
SELECT INTO
Copies data from one table into a new table Example SELECT DISTINCT Country FROM Customers;
66
SELECT TOP
specifies the number of records to return in the result set Example SELECT TOP 3 * FROM Customers;
67
SET
specifies which column and values that should be updated Example UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
68
TABLE
``` creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table Example CREATE TABLE TestTable AS SELECT customername, contactname FROM customers; ```
69
TOP
specifies the number of records to return in the result set Example SELECT TOP 3 * FROM Customers;
70
TRUNCATE TABLE
deletes the data inside a table but not the table itself Example TRUNCATE TABLE Categories;
71
UNION
``` combines the result set of two or more SELECT statements (only distinct values) Example SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City; ```
72
UNION ALL
``` combines the result set of two or more SELECT statements (allows duplicate values) Example SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City; ```
73
UNIQUE
``` a constraint that ensure that all values in a column are unique Example CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) ); ```
74
UPDATE
``` updates existing rows in a table Example UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1; ```
75
VALUES
specifies the values of an INSERT INTO statement Example INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
76
VIEW
``` creates, updates or deletes a view Example CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = "Brazil"; ```
77
WHERE
filters a result set to include only records that fulfil a specified condition Example SELECT * FROM Customers WHERE Country='Mexico';

70-761 (12 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions