SQL

SELECT DISTINCT

Selects Every unique value in a column

Order by ‘Age’ Descending

ORDER BY age DESC

How do you insert age = 9 and name = bob into every table column (table)?

INSERT INTO table

VALUES 9,bob

What is used to test if a value is NULL?

IS NULL

Update table ‘table’ so that column1 contains ‘5’

UPDATE table

SET column1 = 5

Update table ‘table’ so that column1 contains ‘5’ for customerID 1

UPDATE table

SET column1 = 5

WHERE customerID = 1

Delete CustomerID 1 in table ‘table’

DELETE table

WHERE CustomerID = 1

What is used to delete an entire table ‘table''

DROP TABLE table

Select the top 3 records from table ‘table’

SELECT TOP 3 * FROM table

Select the smallest price from column price in table ‘table’

SELECT MIN(Price) FROM table

Select the largest price from column ‘price’ in table ‘table’

SELECT MAX(Price) FROM table

Count the number of products in table ‘table’ that’s not NULL

SELECT COUNT(products) FROM table

Add all customerID’s together in table ‘table’

SELECT SUM(customerID) FROM table

Find the average price of products in table ‘table’

SELECT AVG(price) FROM table

Select all customers whose names start with ‘s’ from table ‘table’

SELECT customer FROM table WHERE customer LIKE ‘s%’

Select all customers whose name has two letters in table ‘table’

SELECT customer FROM table WHERE customer LIKE ‘__’

Select all from table ‘table’ where country = UK, Spain or US

SELECT * FROM table WHERE country IN (‘UK’,’SPAIN’,’US’)

Select all customers in table ‘table’ who have an order in table ‘orders’

SELECT customers FROM table WHERE Customer IN (SELECT customer FROM orders)

Select all customers from table ‘table’ where their age is between 10 and 20

SELECT customer FROM table WHERE age BETWEEN 10 AND 20

Select all customers from table ‘table’ and display it as customername from table ‘table’

SELECT customers AS customername FROM table

Join tables Orders and Customers on customer ID

SELECT * FROM Customers INNER JOIN Orders ON CustomerID.Customers = OrderID.Orders

Left Join tables Orders and Customers on customerID

SELECT * FROM Customers LEFT JOIN Orders ON customerID.Customers = customerID.Orders

In a Left Join, what records will be returned?

Records from the left table and records in both the left and right table.

Full join tables Orders and Customers on customerID

SELECT * FROM Orders FULL JOIN CustomerID.Customers = CustomerID.Orders

Select Distinct cities from Customers and Suppliers

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers

Select cities (Including Duplicates) from Customers and Suppliers

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers

Count the number of customerID’s in each city in table ‘table’

SELECT COUNT(CustomerID), City FROM table GROUP BY City

When is HAVING used instead of WHERE?

When aggregate functions are used

make a backup of table ‘customers’ called ‘customersbackup’

SELECT * INTO CustomersBackup FROM Customers

Make a backup of table customers called customersbackup In an external database called externaldatabase

SELECT * INTO Customersbackup IN ‘externaldatabase’ FROM Customers

Insert table Orders Into table customers(Customers conatains ‘ customername’ and Orders contains ‘ OrderNumber’

INSERT INTO Customers(customer name) SELECT Orders (ordernumber) FROM Orders

In table customers, if customername is bob save to ‘output’ ‘name is bob’ else save ‘name is not bob’

SELECT Customername
CASE

WHEN customer name = ‘bob’ THEN ‘name is bob’

ELSE ‘name is not bob’

END AS output

FROM Customers

What does SELECT IFNULL(Orders,0) FROM table

Returns Order and 0 if orders is NULL