Database

1. Get data of Name and Country fields of users whose favourite food is Candy in the customer table

SELECT Name, Country from Customer WHERE Favorite_Food = “Candy”

2. Get data of all the fields of users whose favourite food is Candy on the customer table

SELECT * FROM Customer WHERE Food = “Candy”

3. Sort the query to get the latest values first

SELECT * FROM Customer WHERE Food = “Candy” ORDER BY CREATED DESC

4. Get how many people like candy

SELECT COUNT(*) FROM Customer WHERE Food = “Candy”

5. Select all the different countries from the “Customers” table (Unique Values Only)

SELECT DISTINCT Country FROM Customer

6. Select all customers from Spain that starts with the letter ‘G’

SELECT * FROM Customer WHERE Country = “Spain” AND Name LIKE ‘G%’
//multiple AND
SELECT * FROM Customers WHERE Country = 'Germany'AND City = 'Berlin'
AND PostalCode > '12000';
//AND and OR
SELECT * FROM Customers WHERE Country = 'Spain'
AND (CustomerName LIKE 'G%' OR City LIKE 'M%');

7. Select all customers from Germany or Spain:

SELECT * FROM Customer WHERE Country = ‘Germany’ OR Country = ‘Spain’

8. Select only the customers that are NOT from Spain

SELECT * FROM WHERE NOT Country = “Spain”

9. Insert Into

INSERT INTO Customer(column1, column2, column3) VALUES(“Spain”, “Candy”, “Asia”)

10. NULL Values

SELECT * FROM Customer WHERE Country IS NULL

11. Update

UPDATE Customer

SET Food = “Candy”, Country = “Spain”

WHERE Age = 18

12. INNER JOIN and OUTER JOIN

SELECT Customer. Name , Customer.ID, Order.OrderID

From Customer
INNER JOIN Order ON Customer.OrderID = Order.OrderID

13. Sort by Alphabetically

SELECT * FROM Customers ORDER BY City

14. Sort based on multiple column names

SELECT * FROM Customers ORDER BY City,Name

Here sorting is done based on the City, if there are multiple Names with the same city those names will be sorted based on the city

15. Sort by both ascending and descending order

SELECT * FROM Customers ORDER BY City ASC,Name DESC

16. Delete SQL

DELETE FROM Customer WHERE Name = "alfred" 

//Delete all the rows

DELETE FROM Customer

//Delete table completely
DROP Customer

17. Min Max and AS and Count and Sum and Average

SELECT MIN(Price) FROM Customer

SELECT MAX(Price) FROM Customer

SELECT MIN(Price) AS smallest FROM Customer
 
SELECT COUNT(*) FROM Customer

SELECT SUM(Price) FROM Customer

SELECT AVG(Price) FROM Customer

18. What is primary key ?

It must contain unique values
It cannot contain null values
A table can have only one primary key
It is used to identify records in a table uniquely

19. What is foreign key?

It can contain duplicate values
It can contain null values
A table can have more than one foreign key
It is used to make relation between two tables
The child table has a special column that references the primary key of the parent table. This column is called the foreign key.