Brainscape's Knowledge GenomeTM

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


See full index

70-761 > QUERY practice > Flashcards

QUERY practice Flashcards

1
Q

Insert the missing statement to get all the columns from the Customers table.

________ * FROM Customers;

A

SELECT * FROM Customers

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

Write a statement that will select the City column from the Customers table.

______ _____ _____ customers;

A

SELECT City FROM Customers;

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

Select all the different values from the Country column in the Customers table.

____ _____ country FROM customers

A

SELECT DISTINCT country FROM customers

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

Select all records where the City column has the value “Berlin”.

SELECT * FROM customers ____ ___ = ______ ;

A

SELECT * FROM customers WHERE city = ‘Berlin’

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

Use the NOT keyword to select all records where City is NOT “Berlin”.

SELECT * FROM customers _________ = _______

A

SELECT * FROM customers WHERE NOT city = ‘Berlin’;

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

Select all records where the CustomerID column has the value 32.

SELECT * FROM customers ______ customer id __ __;

A

SELECT * FROM customers WHERE customerID = 32;

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

Select all records where the City column has the value ‘Berlin’ and the PostalCode column has the value 12209.

______ * FROM customers ___ city = ‘Berlin’ __ ____ = 12209

A

SELECT * FROM customers WHERE city = ‘Berlin’ AND postalcode = 12209

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

Select all records where the City column has the value ‘Berlin’ or ‘London’.

______ * FROM customers ___ city = ‘Berlin’ __ ___ = _______

A

SELECT * FROM customers WHERE City = ‘Berlin’ OR city = ‘London’

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

Select all records from the Customers table, sort the result alphabetically by the column City.

SELECT * FROM customers ______ _____

A

SELECT * FROM customers ORDER BY city;

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

Select all records from the Customers table, sort the result reversed alphabetically by the column City.

SELECT * FROM customers ____ ___ ___ ;

A

SELECT * FROM customers ORDER BY city DESC

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

Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.

SELECT * FROM customers _____ ___ ____;

A

SELECT * FROM customers ORDER BY country, city;

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

Insert a new record in the Customers table.

_____ Customers __ customername, address, city, postalcode, country__
_____ __ ‘Hekkan Burger’, ‘Gatevein 15’, ‘Sandnes’, ‘4306’, ‘Norway’__;

A

INSERT INTO Customers (customername, address, city, postalcode, country)
VALUES (‘Hekkan Burger’, ‘Gatevein 15’, ‘Sandnes’, ‘4306’, ‘Norway’);

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

Select all records from the Customers where the PostalCode column is empty.

SELECT * FROM customers WHERE ____ __ ____;

A

SELECT * FROM customers WHERE postalcode IS NULL;

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

Select all records from the Customers where the PostalCode column is NOT empty.

SELECT * FROM customers WHERE ___ ___ ___ ____;

A

SELECT * FROM customers WHERE postalcode IS NOT NULL

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

Update the City column of all records in the Customers table.

________ customers ___ city = ‘Oslo’

A

UPDATE customers SET city = ‘Oslo’;

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

Set the value of the City columns to ‘Oslo’, but only the ones where the Country column has the value “Norway”.

_____ customers __ city = ‘oslo’ ____ country = ‘Norway’

A

UPDATE customers SET city = ‘Oslo’ WHERE country = ‘Norway’

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

Update the City value and the Country value.

_____ customers __ city = ‘Oslo’ __ ______ = ‘Norway’ WHERE customerID = 32

A

UPDATE customers SET city = ‘Oslo’, country = ‘Norway’ WHERE customerID = 32

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

Delete all the records from the Customers table where the Country value is ‘Norway’.

_______ ______ customers _____ country =’Norway’

A

DELETE FROM customers WHERE country = ‘Norway’

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

Delete all the records from the Customers table.

________ customers;

A

DELETE FROM customers

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

Use the MIN function to select the record with the smallest value of the Price column.

SELECT ______ FROM products

A

SELECT MIN(price) FROM products

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

Use an SQL function to select the record with the highest value of the Price column.

SELECT________ FROM products

A

SELECT MAX(price) FROM products

22
Q

Use the correct function to return the number of records that have the Price value set to 18.

SELECT ____ (*) FROM products ____ price = 18

A

SELECT COUNT(*) FROM products WHERE price = 18

23
Q

Use an SQL function to calculate the average price of all products.

SELECT ________ FROM products

A

SELECT AVG(price) FROM products

24
Q

Use an SQL function to calculate the sum of all the Price column values in the Products table.

SELECT _____ FROM products

A

SELECT SUM(price) FROM products

25
Q

Select all records where the value of the City column starts with the letter “a”.

SELECT * FROM customers _______ ________

A

SELECT * FROM customers WHERE city LIKE ‘a%’

26
Q

Select all records where the value of the City column ends with the letter “a”.

SELECT * FROM customers ____ ___ ___ ___

A

SELECT * FROM customers WHERE city LIKE ‘%a’

27
Q

Select all records where the value of the City column contains the letter “a”.

SELECT * FROM customers ___ _____ ____ __

A

SELECT * FROM customers WHERE city LIKE ‘%a%’

28
Q

Select all records where the value of the City column starts with letter “a” and ends with the letter “b”.

SELECT * FROM customers ___ _____ ____ __

A

SELECT * FROM customers WHERE city LIKE ‘a%b’

29
Q

Select all records where the value of the City column does NOT start with the letter “a”.

SELECT * FROM customers ___ _____ ____ ___ __

A

SELECT * FROM customers WHERE city NOT LIKE ‘a%’

30
Q

Select all records where the second letter of the City is an “a”.

SELECT * FROM customers WHERE city LIKE ‘_%’

A

SELECT * FROM customers WHERE city LIKE ‘_a%’

31
Q

Select all records where the first letter of the City is an “a” or a “c” or an “s”.

SELECT * FROM customers WHERE city LIKE ‘___%’

A

SELECT * FROM customers WHERE city LIKE ‘[acs]%’

32
Q

Select all records where the first letter of the City starts with anything from an “a” to an “f”.

SELECT * FROM customers WHERE city LIKE ‘____%’

A

SELECT * FROM customers WHERE city LIKE ‘[a-f]%’

33
Q

Select all records where the first letter of the City is NOT an “a” or a “c” or an “f”.

SELECT * FROM customers WHERE city LIKE ‘____%’

A

SELECT * FROM customers WHERE city LIKE ‘[^acf]%’

34
Q

Use the IN operator to select all the records where Country is either “Norway” or “France”.

SELECT * FROM customers _____ _____ ‘France’ _

A

SELECT * FROM customers WHERE country IN (‘Norway’, ‘France’)

35
Q

Use the IN operator to select all the records where Country is NOT “Norway” and NOT “France”.

SELECT * FROM customers _____ ___ __ (‘Norway’, ‘France’)

A

SELECT * FROM customers WHERE country NOT IN (‘Norway’, ‘France’)

36
Q

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.

SELECT * FROM products WHERE price ______

A

SELECT * FROM products WHERE price BETWEEN 10 AND 20

37
Q

Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.

SELECT * FROM products WHERE price ______

A

SELECT * FROM products WHERE price NOT BETWEEN 10 AND 20

38
Q

Use the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between ‘Geitost’ and ‘Pavlova’.

SELECT * FROM products WHERE productname _________

A

SELECT * FROM products WHERE productname BETWEEN ‘Geitost’ AND ‘Pavlova’

39
Q

When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead.

SELECT customername, address, postalcode ____ FROM customers

A

SELECT customername, address, postalcode AS pno FROM customers

40
Q

When displaying the Customers table, refer to the table as Consumers instead of Customers.

SELECT * FROM customers ________

A

SELECT * FROM customers AS Consumers

41
Q

Insert the missing parts in the JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables.

SELECT * FROM orders
LEFT JOIN Customers _________ = ___________

A

SELECT * FROM orders
LEFT JOIN Customers
ON orders.customerid=customers.customerID

42
Q

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.

SELECT * FROM orders

________________ ON orders.customerID=customers.customerID

A

SELECT * FROM orders
INNER JOIN Customers
ON orders.customerid=customers.customerID

43
Q

Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.

SELECT * FROM orders
________________ ON
orders.customerID=customers.customerID

A

SELECT * FROM orders
RIGHT JOIN Customers
ON orders.customerid=customers.customerID

44
Q

List the number of customers in each country.

SELECT ____(customerID), country FROM customers _________

A 
SELECT COUNT(customerID), Country FROM customers
GROUP BY country
45
Q

List the number of customers in each country, ordered by the country with the most customers first.

SELECT \_\_\_\_\_\_\_ (CustomerID), country FROM customers \_\_\_\_\_\_\_\_\_\_ 
ORDER BY \_\_\_\_\_\_\_\_\_\_\_\_\_
A 
SELECT COUNT(customerID), Country FROM customers
GROUP BY country
ORDER BY COUNT(CustomerID) DESC
46
Q

Write the correct SQL statement to create a new database called testDB.

A

CREATE DATABASE testdb

47
Q

Write the correct SQL statement to delete a database named testDB.

A

DROP DATABASE testDB

48
Q

Write the correct SQL statement to create a new table called Persons.

\_\_\_\_\_\_\_\_\_\_\_\_\_
 (  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255) );
A 
CREATE TABLE persons
 (  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255) )
49
Q

Write the correct SQL statement to delete a table called Persons.

A

DROP TABLE persons

50
Q

Use the TRUNCATE statement to delete all data inside a table.

A

TRUNCATE TABLE persons

51
Q

Add a column of type DATE called Birthday.

A

ALTER TABLE persons

ADD birthday DATE

52
Q

Delete the column Birthday from the Persons table.

A

ALTER TABLE persons

DROP COLUMN birthday

70-761 (12 decks)

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