Brainscape's Knowledge GenomeTM

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


See full index

SQL > SQL commands > Flashcards

SQL commands Flashcards

1
Q

The following SQL statement selects all the records in the “Customers” table:

A

SELECT * FROM Customers;

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

The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table:

A

SELECT CustomerName,City FROM Customers;

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

The following SQL statement selects only the distinct values from the “City” columns from the “Customers” table:

A

SELECT DISTINCT City FROM Customers;

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

The following SQL statement selects all the customers from the country “Mexico”, in the “Customers” table:

A

SELECT * FROM Customers WHERE Country=’Mexico’;

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

The following SQL statement selects all the customers whose CustomerID is equal to 1, in the “Customers” table:

A

SELECT * FROM Customers WHERE CustomerID=1;

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

The following SQL statement selects all customers from the country “Germany” AND the city “Berlin”, in the “Customers” table:

A

SELECT * FROM Customers WHERE Country=’Germany’ AND City=’Berlin’;

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

The following SQL statement selects all customers from the city “Berlin” OR “München”, in the “Customers” table:

A

SELECT * FROM Customers WHERE City=’Berlin’ OR City=’München’;

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

The following SQL statement selects all customers from the country “Germany” AND the city must be equal to “Berlin” OR “München”, in the “Customers” table:

A

SELECT * FROM Customers WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);

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

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” column:

A

SELECT * FROM Customers ORDER BY Country;

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

The following SQL statement selects all customers from the “Customers” table, sorted DESCENDING by the “Country” column:

A

SELECT * FROM Customers ORDER BY Country DESC;

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

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column:

A

SELECT * FROM Customers ORDER BY Country,CustomerName;

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

The following SQL statement will insert a new row, but only insert data in the “CustomerName”, “City”, and “Country” columns, with ‘Cardinal’, ‘Stavanger’, and ‘Norway’, respectively.

A

INSERT INTO Customers (CustomerName, City, Country) VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

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

The following SQL statement will update the customer “Alfreds Futterkiste” with the ContactName ‘Alfred Schmidt’ and the City ‘Hamburg’.

A

UPDATE Customers SET ContactName=’Alfred Schmidt’, City=’Hamburg’ WHERE CustomerName=’Alfreds Futterkiste’;

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

Assume we wish to delete the customer “Alfreds Futterkiste” from the “Customers” table.

A

DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;

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

The following SQL statement will delete all the rows from Customers [2]

A
  1. DELETE FROM Customers; 2. DELETE * FROM Customers;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

The following SQL statement selects the two first records from the “Customers” table:

A

SELECT TOP 2 * FROM Customers;

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

The following SQL statement selects the first 50% of the records from the “Customers” table:

A

SELECT TOP 50 PERCENT * FROM Customers;

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

The following SQL statement selects all customers with a City starting with the letter “s”:

A

SELECT * FROM Customers WHERE City LIKE ‘s%’;

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

The following SQL statement selects all customers with a City ending with the letter “s”:

A

SELECT * FROM Customers WHERE City LIKE ‘%s’;

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

The following SQL statement selects all customers with a Country containing the pattern “land”:

A

SELECT * FROM Customers WHERE Country LIKE ‘%land%’;

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

The following SQL statement selects all customers with a Country NOT containing the pattern “land”:

A

SELECT * FROM Customers WHERE Country NOT LIKE ‘%land%’;

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

The following SQL statement selects all customers with a City starting with “ber”:

A

SELECT * FROM Customers WHERE City LIKE ‘ber%’;

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

The following SQL statement selects all customers with a City containing the pattern “es”:

A

SELECT * FROM Customers WHERE City LIKE ‘%es%’;

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

The following SQL statement selects all customers with a City starting with any character, followed by “erlin”:

A

SELECT * FROM Customers WHERE City LIKE ‘_erlin’;

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

The following SQL statement selects all customers with a City starting with “L”, followed by any character, followed by “n”, followed by any character, followed by “on”:

A

SELECT * FROM Customers WHERE City LIKE ‘L_n_on’;

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

The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

A

SELECT * FROM Customers WHERE City LIKE ‘[bsp]%’;

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

The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

A

SELECT * FROM Customers WHERE City LIKE ‘[a-c]%’;

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

The following SQL statement selects all customers with a City NOT starting with “b”, “s”, or “p”:

A

SELECT * FROM Customers WHERE City LIKE ‘[!bsp]%’;

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

The following SQL statement selects all customers with a City of “Paris” or “London”:

A

SELECT * FROM Customers WHERE City IN (‘Paris’,’London’);

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

The following SQL statement selects all products with a price BETWEEN 10 and 20:

A

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

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

To display the products outside the range of the previous example, use NOT BETWEEN:

A

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

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

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

A

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

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

The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’:

A

SELECT * FROM Products WHERE ProductName BETWEEN ‘C’ AND ‘M’;

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

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN ‘C’ and ‘M’:

A

SELECT * FROM Products WHERE ProductName NOT BETWEEN ‘C’ AND ‘M’;

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

The following SQL statement selects all orders with an OrderDate BETWEEN ‘04-July-1996’ and ‘09-July-1996’:

A

SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

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

The following SQL statement specifies two aliases, one for the CustomerName column and one for the ContactName column, “Customer” and “Contact Name”, respectively.

A

SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

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

In the following SQL statement we combine four columns (Address, City, PostalCode, and Country) and create an alias named “Address”:

A

SELECT CustomerName, Address+’, ‘+City+’, ‘+PostalCode+’, ‘+Country AS Address FROM Customers;

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

The following SQL statement selects all the orders from the customer with CustomerName = “Around the Horn” with CustomerName column from the Customers table and OrderID and OrderDate from the Orders Table. We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively.

A

SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName=”Around the Horn”

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

The following SQL statement selects all the orders from the customer with CustomerName = “Around the Horn” with CustomerName column from the Customers table and OrderID and OrderDate from the Orders Table.

A

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName=”Around the Horn”

40
Q

The following SQL statement selects all rows from the Customers and Orders tables, displaying the CustomerName column from Customers and the OrderID column from Orders, as long as CustomerID’s match for both tables. ordered by CustomerName in Customers Table

A

SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

41
Q

The following SQL statement selects all rows from the Customers table, AND all rows from the Orders table, displaying the CustomerName column from Customers and the OrderID column from Orders, as long as CustomerID’s match for both tables. ordered by CustomerName in Customers Table

A

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

42
Q

The following SQL statement selects all rows from the Employees table, AND all rows from the Orders table, displaying the OrderID column from Orders and the FirstName column from Employees, as long as EmployeeID’s match for both tables. ordered by OrderID in Orders Table

A

SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID;

43
Q

The following SQL statement selects all rows from the Customers table AND all rows from the Orders table, displaying the CustomerName column from Customers and the OrderID column from Orders, matching CustomerID’s. ordered by CustomerName in Customers Table

A

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

44
Q

The following SQL statement selects all the different cities (only distinct values) from the “Customers” and the “Suppliers” tables, ordered by City:

A

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

45
Q

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the “Customers” and “Suppliers” tables, ordered by City:

A

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;

46
Q

The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the “Customers” and “Suppliers” tables, ordered by City:

A

SELECT City, Country FROM Customers WHERE Country=’Germany’ UNION ALL SELECT City, Country FROM Suppliers WHERE Country=’Germany’ ORDER BY City;

47
Q

SELECT * FROM Customers;

A

The following SQL statement selects all the records in the “Customers” table:

48
Q

SELECT CustomerName,City FROM Customers;

A

The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table:

49
Q

SELECT DISTINCT City FROM Customers;

A

The following SQL statement selects only the distinct values from the “City” columns from the “Customers” table:

50
Q

SELECT * FROM Customers WHERE Country=’Mexico’;

A

The following SQL statement selects all the customers from the country “Mexico”, in the “Customers” table:

51
Q

SELECT * FROM Customers WHERE CustomerID=1;

A

The following SQL statement selects all the customers whose CustomerID is equal to 1, in the “Customers” table:

52
Q

SELECT * FROM Customers WHERE Country=’Germany’ AND City=’Berlin’;

A

The following SQL statement selects all customers from the country “Germany” AND the city “Berlin”, in the “Customers” table:

53
Q

SELECT * FROM Customers WHERE City=’Berlin’ OR City=’München’;

A

The following SQL statement selects all customers from the city “Berlin” OR “München”, in the “Customers” table:

54
Q

SELECT * FROM Customers WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);

A

The following SQL statement selects all customers from the country “Germany” AND the city must be equal to “Berlin” OR “München”, in the “Customers” table:

55
Q

SELECT * FROM Customers ORDER BY Country;

A

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” column:

56
Q

SELECT * FROM Customers ORDER BY Country DESC;

A

The following SQL statement selects all customers from the “Customers” table, sorted DESCENDING by the “Country” column:

57
Q

SELECT * FROM Customers ORDER BY Country,CustomerName;

A

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column:

58
Q

INSERT INTO Customers (CustomerName, City, Country) VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

A

The following SQL statement will insert a new row, but only insert data in the “CustomerName”, “City”, and “Country” columns, with ‘Cardinal’, ‘Stavanger’, and ‘Norway’, respectively.

59
Q

UPDATE Customers SET ContactName=’Alfred Schmidt’, City=’Hamburg’ WHERE CustomerName=’Alfreds Futterkiste’;

A

The following SQL statement will update the customer “Alfreds Futterkiste” with the ContactName ‘Alfred Schmidt’ and the City ‘Hamburg’.

60
Q

DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;

A

Assume we wish to delete the customer “Alfreds Futterkiste” from the “Customers” table.

61
Q
  1. DELETE FROM Customers; 2. DELETE * FROM Customers;
A

The following SQL statement will delete all the rows from Customers [2]

62
Q

SELECT TOP 2 * FROM Customers;

A

The following SQL statement selects the two first records from the “Customers” table:

63
Q

SELECT TOP 50 PERCENT * FROM Customers;

A

The following SQL statement selects the first 50% of the records from the “Customers” table:

64
Q

SELECT * FROM Customers WHERE City LIKE ‘s%’;

A

The following SQL statement selects all customers with a City starting with the letter “s”:

65
Q

SELECT * FROM Customers WHERE City LIKE ‘%s’;

A

The following SQL statement selects all customers with a City ending with the letter “s”:

66
Q

SELECT * FROM Customers WHERE Country LIKE ‘%land%’;

A

The following SQL statement selects all customers with a Country containing the pattern “land”:

67
Q

SELECT * FROM Customers WHERE Country NOT LIKE ‘%land%’;

A

The following SQL statement selects all customers with a Country NOT containing the pattern “land”:

68
Q

SELECT * FROM Customers WHERE City LIKE ‘ber%’;

A

The following SQL statement selects all customers with a City starting with “ber”:

69
Q

SELECT * FROM Customers WHERE City LIKE ‘%es%’;

A

The following SQL statement selects all customers with a City containing the pattern “es”:

70
Q

SELECT * FROM Customers WHERE City LIKE ‘_erlin’;

A

The following SQL statement selects all customers with a City starting with any character, followed by “erlin”:

71
Q

SELECT * FROM Customers WHERE City LIKE ‘L_n_on’;

A

The following SQL statement selects all customers with a City starting with “L”, followed by any character, followed by “n”, followed by any character, followed by “on”:

72
Q

SELECT * FROM Customers WHERE City LIKE ‘[bsp]%’;

A

The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

73
Q

SELECT * FROM Customers WHERE City LIKE ‘[a-c]%’;

A

The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

74
Q

SELECT * FROM Customers WHERE City LIKE ‘[!bsp]%’;

A

The following SQL statement selects all customers with a City NOT starting with “b”, “s”, or “p”:

75
Q

SELECT * FROM Customers WHERE City IN (‘Paris’,’London’);

A

The following SQL statement selects all customers with a City of “Paris” or “London”:

76
Q

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

A

The following SQL statement selects all products with a price BETWEEN 10 and 20:

77
Q

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

A

To display the products outside the range of the previous example, use NOT BETWEEN:

78
Q

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

A

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

79
Q

SELECT * FROM Products WHERE ProductName BETWEEN ‘C’ AND ‘M’;

A

The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’:

80
Q

SELECT * FROM Products WHERE ProductName NOT BETWEEN ‘C’ AND ‘M’;

A

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN ‘C’ and ‘M’:

81
Q

SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

A

The following SQL statement selects all orders with an OrderDate BETWEEN ‘04-July-1996’ and ‘09-July-1996’:

82
Q

SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

A

The following SQL statement specifies two aliases, one for the CustomerName column and one for the ContactName column, “Customer” and “Contact Name”, respectively.

83
Q

SELECT CustomerName, Address+’, ‘+City+’, ‘+PostalCode+’, ‘+Country AS Address FROM Customers;

A

In the following SQL statement we combine four columns (Address, City, PostalCode, and Country) and create an alias named “Address”:

84
Q

SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName=”Around the Horn”

A

The following SQL statement selects all the orders from the customer with CustomerName = “Around the Horn” with CustomerName column from the Customers table and OrderID and OrderDate from the Orders Table. We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively.

85
Q

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName=”Around the Horn”

A

The following SQL statement selects all the orders from the customer with CustomerName = “Around the Horn” with CustomerName column from the Customers table and OrderID and OrderDate from the Orders Table.

86
Q

SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

A

The following SQL statement selects all rows from the Customers and Orders tables, displaying the CustomerName column from Customers and the OrderID column from Orders, as long as CustomerID’s match for both tables. ordered by CustomerName in Customers Table

87
Q

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

A

The following SQL statement selects all rows from the Customers table, AND all rows from the Orders table, displaying the CustomerName column from Customers and the OrderID column from Orders, as long as CustomerID’s match for both tables. ordered by CustomerName in Customers Table

88
Q

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

A

The following SQL statement selects all rows from the Customers table, AND all rows from the Orders table, displaying the CustomerName column from Customers and the OrderID column from Orders, as long as CustomerID’s match for both tables. ordered by CustomerName in Customers Table

89
Q

SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID;

A

The following SQL statement selects all rows from the Employees table, AND all rows from the Orders table, displaying the OrderID column from Orders and the FirstName column from Employees, as long as EmployeeID’s match for both tables. ordered by OrderID in Orders Table

90
Q

SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID;

A

The following SQL statement selects all rows from the Employees table, AND all rows from the Orders table, displaying the OrderID column from Orders and the FirstName column from Employees, as long as EmployeeID’s match for both tables. ordered by OrderID in Orders Table

91
Q

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

A

The following SQL statement selects all rows from the Customers table AND all rows from the Orders table, displaying the CustomerName column from Customers and the OrderID column from Orders, matching CustomerID’s. ordered by CustomerName in Customers Table

92
Q

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

A

The following SQL statement selects all the different cities (only distinct values) from the “Customers” and the “Suppliers” tables, ordered by City:

93
Q

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;

A

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the “Customers” and “Suppliers” tables, ordered by City:

94
Q

SELECT City, Country FROM Customers WHERE Country=’Germany’ UNION ALL SELECT City, Country FROM Suppliers WHERE Country=’Germany’ ORDER BY City;

A

The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the “Customers” and “Suppliers” tables, ordered by City:

95
Q

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

A

The following SQL statement selects all rows from the Customers table AND all rows from the Orders table, displaying the CustomerName column from Customers and the OrderID column from Orders, matching CustomerID’s. ordered by CustomerName in Customers Table

SQL (1 decks)

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