SQL

1. = 2. > 3. < 4. >= 5. <= 6. <> 7. BETWEEN 8. LIKE 9. IN

1. equal 2. greater than 3. less than 4. greater than or equal 5. less than or equal 6. Not equal (or !=) 7. between a certain range 8. search for a pattern 9. to specify multiple possible values for a column

Describe the following types of JOINS in SQL: 1. (INNER) JOIN 2. LEFT JOIN -OUTER JOIN 3. RIGHT JOIN -OUTER JOIN 4. FULL JOIN -OUTER JOIN

1. matching values from both tables 2. from left table and matching values 3. from right table and matching values 4. everything from both tables

1. a% 2.%a 3. %a% 4. _a% 5. a_% 6. a__% 7. a%a

1. start with 'a' 2. end with 'a' 3. have 'a' in any position 4. 'a' in second position 5. start with 'a' and have at least 2 characters in length 6. start with 'a' and have at least 3 characters in length 7. start with 'a' and ends with 'a'

What is a NULL value?

A field with no value. Blank during record creation. Different from a zero value or spaces.

Write a SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table.

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Write a SQL statement deletes all rows in the "Customers" table, without deleting the table.

DELETE FROM Customers; # Note that the table structure, attributes, and indexes will still be intact.

T/F It is possible to test for NULL values with comparison operators, such as =, <, or <>.

F It is NOT possible to test for NULL values with comparison operators, such as =, <, or <>.

T/F: SQL keywords are case sensitive.

F. SQL keywords are NOT case sensitive: select is the same as SELECT.

Write a SQL statement will selectively insert data in the "CustomerName", "City", and "Country" columns: CustomerName: Cardinal City: Stavanger Country: Norway

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway'); #Note that CustomerID is updated automatically.

Write a SQL statement inserts a new record in the "Customers" table: Customer Name: Cardinal Contact Name: Tom B. Erichsen Address: Skagen 21 City: Stavanger Postal Code: 4006 Country: Norway

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Describe the IS NULL operator. What makes it different from NULL value?

It is used to test/look for empty values(NULL values)

What do you need to be careful when it comes to updating records?

Remember to use WHERE clause, otherwise all records will be updated.

Write a SQL statement that selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column.

SELECT * FROM Customers ORDER BY Country DESC;

Write a SQL statement that selects all customers from the "Customers" table, sorted by the "Country" column.

SELECT * FROM Customers ORDER BY Country;

Write a SQL statement that selects all fields from "Customers" where country is "Germany" AND City is "Berlin."

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

Write a SQL statement that selects all the customers from the country "Mexico", in the "Customers" table.

SELECT * FROM Customers WHERE Country='Mexico';

Write a SQL statement selects all customers with a CustomerName starting with "a".

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

Write a SQL statement that selects all fields from "Customers" where country is NOT "Germany" and NOT "USA"

SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';

Write a SQL statement that lists all customers with a value in the "Address" field

SELECT * FROM Customers WHERE Address IS NOT NULL;

Write a 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".

SELECT * FROM Customers WHERE City LIKE 'L_n_on';

Write a SQL statement selects all customers that are from the same countries as the suppliers.

SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

Write a SQL statement that selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München."

SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); #use parenthesis to form complex expressions

Write a SQL statement that selects all customers with a CustomerName that does NOT start with "a"

SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';

Write a SQL statement that selects all fields from "Customers" where country is NOT "Germany." (2 different ways)

SELECT * FROM Customers WHERE NOT Country='Germany'; or SELECT * FROM Customers WHERE Country<>'Germany';

Write a SQL statement that selects all the columns from the "Customers" table.

SELECT * FROM Customers; # * means 'all columns' # when you call column name do not treat it as a text value(use apostrophe.)

Write a SQL statement that shows the equivalent example using the LIMIT clause (for MySQL)

SELECT * FROM CustomersLIMIT 3;

Write a SQL statement that selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column.

SELECT * FROM CustomersORDER BY Country ASC, CustomerName DESC (Use of ASC is negligible here)

Write a SQL statement that selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column.

SELECT * FROM CustomersORDER BY Country, CustomerName; Notice the the order of Country is alphabetized first and then the CustomerName.

Write a SQL statement that selects all customers with a City containing the pattern "es".

SELECT * FROM CustomersWHERE City LIKE '%es%';

Write a SQL statement that selects all customers with a City NOT starting with "b", "s", or "p" (2 different ways).

SELECT * FROM CustomersWHERE City LIKE '[!bsp]%'; OR SELECT * FROM CustomersWHERE City NOT LIKE '[bsp]%';

Write a SQL statement that selects all customers with a City starting with "a", "b", or "c".

SELECT * FROM CustomersWHERE City LIKE '[a-c]%';

Write a SQL statement that selects all customers with a City starting with any character, followed by "ondon".

SELECT * FROM CustomersWHERE City LIKE '_ondon';

Write a SQL statement that selects all customers with a City starting with "ber".

SELECT * FROM CustomersWHERE City LIKE 'ber%';

Write a SQL statement that selects all fields from "Customers" where city is "Berlin" OR "München."

SELECT * FROM CustomersWHERE City='Berlin' OR City='München';

Write a SQL statement that selects all customers that are located in "Germany", "France" or "UK".

SELECT * FROM CustomersWHERE Country IN ('Germany', 'France', 'UK');

Write a SQL statement selects all customers that are NOT located in "Germany", "France" or "UK".

SELECT * FROM CustomersWHERE Country NOT IN ('Germany', 'France', 'UK');

Write a statement that brings a customer with the "CustomerID" of 1 from "Customers" table.

SELECT * FROM CustomersWHERE CustomerID=1;

Write a SQL statement that selects all customers with a CustomerName ending with "a"

SELECT * FROM CustomersWHERE CustomerName LIKE '%a';

Write a SQL statement that selects all customers with a CustomerName that have "or" in any position

SELECT * FROM CustomersWHERE CustomerName LIKE '%or%';

Write a SQL statement that shows the equivalent example using ROWNUM (for Oracle)

SELECT * FROM CustomersWHERE ROWNUM <= 3;

Write a SQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996'. (2 different ways)

SELECT * FROM Orders WHERE Order Date BETWEEN #01/07/1996# AND #31/07/1996#; or SELECT * FROM OrdersWHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Write a SQL statement selects all products with a price BETWEEN 10 and 20.

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

Write a SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3.

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

Write a SQL statement that display the products outside the range of the previous example.

SELECT * FROM ProductsWHERE Price NOT BETWEEN 10 AND 20;

Write a SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Chef Anton's Cajun Seasoning.

SELECT * FROM ProductsWHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"ORDER BY ProductName;

Write a SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Mozzarella di Giovanni.

SELECT * FROM ProductsWHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'ORDER BY ProductName;

Write a SQL statement selects all products with a ProductName NOT BETWEEN Carnarvon Tigers and Mozzarella di Giovanni.

SELECT * FROM ProductsWHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'ORDER BY ProductName;

Write a SQL statement finds the average price of all products.

SELECT AVG(Price) FROM Products;

In SQL, count the number of all the different countries from the "Customers" table.

SELECT COUNT(DISTINCT Country) FROM Customers;

Write a SQL statement finds the number of products.

SELECT COUNT(ProductID)FROM Products;

For Firefox that use MS Access databases, create a different SQL statement that counts the number of all the different countries from the "Customers" table.

SELECT Count(*) AS DistinctCountriesFROM (SELECT DISTINCT Country FROM Customers);

Write a SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column.

SELECT CustomerID AS ID, CustomerName AS CustomerFROM Customers;

Write a SQL statement that creates two aliases, one for the CustomerName column and one for the ContactName column.

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

Write a SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country).

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS AddressFROM Customers;

Write a SQL statement that selects the "CustomerName" and "City" columns from the "Customers" table.

SELECT CustomerName, City FROM Customers;

Write a SQL statement that lists all customers with a NULL value in the "Address" field

SELECT CustomerName, ContactName, AddressFROM CustomersWHERE Address IS NULL;

Write a SQL statement that selects only the DISTINCT values from the "Country" column in the "Customers" table.

SELECT DISTINCT Country FROM Customers;

Write a SQL statement finds the price of the most expensive product.

SELECT MAX(Price) AS LargestPrice FROM Products;

Write a SQL statement finds the price of the cheapest product.

SELECT MIN(Price) AS SmallestPrice FROM Products;

Write a SQL statement selects all orders with customer information.

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

Write a SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDateFROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Write a SQL statement that is the same as above, but without aliases.

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

Write a SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table.

SELECT SUM(Quantity) FROM OrderDetails;

Write a SQL statement that selects the first three records from the "Customers" table (for SQL Server/MS Access)

SELECT TOP 3 * FROM Customers;

Write a SQL statement selects the first 50% of the records from the "Customers" table (for SQL Server/MS Access).

SELECT TOP 50 PERCENT * FROM Customers;

Write a SQL statement that selects all the orders from the customer with CustomerID=4 (Around the Horn)

SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID; #We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter)

Write a SQL statement that selects all fields from "Customers" where country is "Germany" OR "Spain"

SELECT* FROM Customers WHERE Country='Germany' OR Country='Spain';

Write a SQL statement that updates the contactname to "Juan" for all records where country is "Mexico."

UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';

Write a SQL statement that updates the first customer (CustomerID = 1) with a new contact person(Alfred Schmidt) and a new city(Frankfurt).

UPDATE CustomersSET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;

What is a wildcard? when do you use?

a character that is used to substitute one or more characters in string

Describe a JOIN clause.

a clause that is used to combine rows from two or more tables

What is a database?

a file that contains one or more tables

Describe a ORDER BY keyword. When do you use?

a keyword that sorts the records in ascending order(by default) or descending order (by using DESC keyword) # Note that ascending order means alphabetized order. A to Z.

What is an IN operator? When do you use?

a shorthand for multiple OR conditions. allows to specify multiple values in a WHERE clause.

Describe an INSERT INTO syntax When do you use?

a statement that is used to insert new records in a table.

What is the UPDATE statement? When do you use?

a statement that is used to modify the existing records in a table.

Describe the IS NOT NULL operator. When do you use?

an operator that is used to test for non-empty values(Not NULL values)

What is BETWEEN operator? when do you use?

an operator that selects values(numbers/text/dates) within a given range. inclusive: begin and end values are included.

What is SELECT TOP clause? When do you use?

clause that is used to specify the number of records to return. useful on large tables with thousands of records boosting performance.

What do you use or use not around text values vs numeric values?

for texts: 'text' for numbers: 1

Describe a WHERE clause. When do you use?

used to filter records/ extract only those records that fulfill a specified condition. WHERE (is followed by) condition.

Describe SQL aliases. When do you use?

used to give a table/column in a table/temporary name. only exists for the duration of the query. Useful when: *There are more than one table involved in a query *Functions are used in the query *Column names are big or not very readable *Two or more columns are combined together