Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

70-761 > Query Keywords > Flashcards

Query Keywords Flashcards

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
Q

DELETE

A

deletes rows from a table
Example
DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;

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

DESC

A

sorts the result set in descending order
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC;

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

DISTINCT

A

selects on distinct(different) values in the result set
Exmaple
SELECT DISTINCT Country FROM Customers;

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

DROP

A

deletes a column, constraint,database, index, table or view
Example
ALTER TABLE Customers
DROP COLUMN ContactName;

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

DROP COLUMN

A

deletes a column in a table
Example
ALTER TABLE Customers
DROP COLUMN ContactName;

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

DROP CONSTRAINT

A

deletes a unique, primary key , foreign key or check constraint
Example
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

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

DROP DATABASE

A

delete an existing SQL database
Example
DROP DATABASE testDB;

32
Q

DROP DEFAULT

A

deletes a DEFAULT constraint
Example
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

33
Q

DROP INDEX

A

deletes and index in a table
Example
ALTER TABLE table_name
DROP INDEX index_name;

34
Q

DROP TABLE

A

deletes an existing table in a database
Example
DROP TABLE Shippers;

35
Q

DROP VIEW

A

deletes a view
Example
DROP VIEW [Brazil Customers];

36
Q

EXEC

A

executes a stored procedure
Example
EXEC SelectAllCustomers;

37
Q

EXISTS

A

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
Q

FOREIGN KEY

A 
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
Q

FROM

A

specifies which table to select or delete data from
Example
SELECT CustomerName, City FROM Customers;

40
Q

FULL OUTER JOIN

A

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
Q

GROUP BY

A 
groups the result set(used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
42
Q

HAVING

A 
used instead of WHERE with aggregate functions
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
43
Q

IN

A

allows you to specify multiple values in a WHERE clause
EXAMPLE
SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

44
Q

INDEX

A

creates or deletes an index in a table
Example
CREATE INDEX idx_lastname
ON Persons (LastName);

45
Q

INNER JOIN

A

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
Q

INSERT INTO

A

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
Q

INSERT INTO SELECT

A

copies data from one table into another table
Example
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

48
Q

IS NULL

A 
tests for empty values
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
49
Q

IS NOT NULL

A 
tests for non-empty values
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
50
Q

JOIN

A

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
Q

LEFT JOIN

A

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
Q

LIKE

A

searches for a specified pattern in a column
Example
SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;

53
Q

LIMIT

A

specifies the number of records to return in the result set
Example
SELECT * FROM Customers
LIMIT 3;

54
Q

NOT

A

only includes rows where a condition is not true
Example
SELECT * FROM Customers
WHERE NOT Country=’Germany’;

55
Q

NOT NULL

A 
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
Q

OR

A

includes rows where either condition is true
Example
SELECT * FROM Customers
WHERE City=’Berlin’ OR City=’München’;

57
Q

ORDER BY

A

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

58
Q

OUTER JOIN

A

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
Q

PRIMARY KEY

A 
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
Q

PROCEDURE

A 
a stored procedure
Example
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
61
Q

RIGHT JOIN

A

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
Q

ROWNUM

A

specified the number of records to return in the result set
Example
SELECT * FROM Customers
WHERE ROWNUM <= 3;

63
Q

SELECT

A

selects data from the database
Example
SELECT CustomerName, City FROM Customers;

64
Q

SELECT DISTINCT

A

Selects only distinct (different) values
Example
SELECT DISTINCT Country FROM Customers;

65
Q

SELECT INTO

A

Copies data from one table into a new table
Example
SELECT DISTINCT Country FROM Customers;

66
Q

SELECT TOP

A

specifies the number of records to return in the result set
Example
SELECT TOP 3 * FROM Customers;

67
Q

SET

A

specifies which column and values that should be updated
Example
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;

68
Q

TABLE

A 
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
Q

TOP

A

specifies the number of records to return in the result set
Example
SELECT TOP 3 * FROM Customers;

70
Q

TRUNCATE TABLE

A

deletes the data inside a table but not the table itself
Example
TRUNCATE TABLE Categories;

71
Q

UNION

A 
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
Q

UNION ALL

A 
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
Q

UNIQUE

A 
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
Q

UPDATE

A 
updates existing rows in a table
Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
75
Q

VALUES

A

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
Q

VIEW

A 
creates, updates or deletes a view
Example
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";
77
Q

WHERE

A

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.
© 2024 Bold Learning Solutions. Terms and Conditions