sql quick reference

By | October 25, 2019

SQL Select:

1. Insert the missing statement to get all the columns from the Employees table.
_______ * from Employees;
Ans: select * from Employees;

2. Write a statement that will select the Name column from the Customers table.
Ans: select name from Customers;

3. Select all the different values from the location column in the Employees table.
select DISTINCT location from Employees;

SQL Where:

1. Select all records from customers table where the City column has the value “Paris”.
select * from Customers where city=’Paris’;

2. Use the NOT keyword to select all records where City is NOT “Paris”.
select * from Customers where NOT city = ‘Paris’;

3. Select all records from Employees table where the EmployeeID column has the value 6235.
SELECT * FROM Employees WHERE EmployeeID = 6235;

4. Select all records where the city column has the value ‘London’ and the zipcode column has the value 90121. (from Employees table).
select * from Employees where city=’London’ AND zipcode=90121;

5. Select all records where the City column has the value ‘Paris’ or ‘London’. (from Customers table)
select * from Customers where city=’Paris’ OR city=’London’;

SQL Order By:

1. Select all records from the Customers table, sort the result alphabetically by the column City.
select * from Customers ORDER BY City;

2. Select all records from the Employees table, sort the result reversed alphabetically by the column EmpName.
SELECT * FROM Employees ORDER BY EmpName DESC;

3. Select all records from the Customers table. Sort the result alphabetically, first by the column Country, then, by the column City.
select * from Customers ORDER BY country, city;

SQL Insert:

1. Insert a new record in the Employees table.
insert into Employees (EmpName, ID, Address, State) values(‘John’,’7001′,’Richmond street’,’Texas’);

SQL Null;
1. Select all records from the Employees where the zipcode column is empty.
select * from Employees where zipcode is NULL;

2. Select all records from the Employees where the zipcode column is NOT empty.
select * from Employees where zipcode is NOT NULL;

SQL Update:
1. Update the City column of all records in the Customers table to ‘Paris’.
update Customers set city=’Paris’;

2. Set the value of the City columns to ‘Paris’, but only the ones where the Country column has the value “London”.
update Customers set city=’Paris’ where Country=’London’;

3. Update the City value and the Country value in Customers table.
update Customers set City=’Paris’, Country=’London’ where CustomerID=’1005′;

SQL Delete:
1. Delete all the records from the Customers table where the Country value is ‘London’.
DELETE from Customes where Country=’London’;

2. Delete all the records from the Customers table.
DELETE from Customers;

SQL Functions:
1. Use the MIN function to select the record with the smallest value of the Salary column.
select MIN(Salary) from Employees;

2. Use the MAX function to select the record with the highest value of the Salary column.
select MAX(Salary) from Employees;

3. Use the correct function to return the numbers of records that have the Salary value set to 50000.
select count(*) from Employees where Salary=50000;

4. Write a SQL query to calculate the average salary of all employees.
select AVG(salary) from Employees;

5. Write a SQL query to calculate the sum of all the salary column values in the Employees table.
select SUM(salary) from Employees;

SQL Like:
1. Write a query to fetch all records where the value of the EmpName column starts with the letter “S”.
select * from Employees where EmpName LIKE ‘S%’;

2. Write a query to fetch all records where the value of the EmpName column ends with the letter “R”.
select * from Employees where EmpName LIKE ‘%R’;

3. Write a query to fetch all records where the value of the EmpName column starts with the letter “S” and ends with letter “R”.
select * from Employees where EmpName LIKE ‘S%R’;

4. Write a query to fetch all records where the part of the EmpName contains letters “SR”.
select * from Employees where EmpName LIKE ‘%SR%’;

5. Write a query to select all records where the value of the EmpName column does NOT start with the letter “S”.
select * from Employees where EmpName NOT LIKE ‘S%’;

SQL Wildcards:
1. Select all records where the second letter of the EmpName is an “A”.
select * from Employees where EmpName LIKE ‘_A%’;

2. Select all records where the first letter of the EmpName is an “A” or a “B” or a “C”.
select * from Employees where EmpName like ‘[ABC]%’;

3. Fetch all records where the first letter of the EmpName starts with anything from an “A” to an “E”.
SELECT * FROM Employees WHERE EmpName LIKE ‘[A-E]%’;

4. Fetch all records where the first letter of the EmpName is NOT an “A” or a “B” or an “C”.
select * from Employees where EmpName like ‘[^ABC]%’;

SQL In:

Use the IN operator to select all the records where Country is either “Japan” or “China”.
select * from Employees where Country IN (‘Japan’,’China’);

Use the IN operator to select all the records where Country is NOT “Japan” or “China”.
select * from Employees where Country NOT IN (‘Japan’,’China’);

SQL Between:

Use the BETWEEN operator to select all the records where the value of the Salary column is between 40000 and 50000.
select * from Employees where salary BETWEEN 40000 AND 50000;

Use the BETWEEN operator to select all the records where the value of the Salary column is not between 40000 and 50000.
select * from Employees where salary NOT BETWEEN 40000 AND 50000;

Use the BETWEEN operator to select all the records where the value of the EmpName column is alphabetically between ‘John’ and ‘James’.
select * from Employees where EmpName BETWEEN ‘John’ AND ‘James’;

SQL Alias:

When displaying the Employees table, make an ALIAS of the zipcode column, the column should be called zip instead.
select EmpName, zipcode AS zip from Employees;

When displaying the Employees table, refer to the table as JobHolders instead of Employees.
select * from Employees AS JobHolders;

SQL Join:

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.
select * from Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID;    or
select * from Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID; //Here c and o are alias names of the tables.

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 RIGHT JOIN Customers where Orders.CustomerID=Customers.CustomerID;

Choose the correct JOIN clause to select all the records from the Orders table plus all the matches in the Customers table.
select * from Orders LEFT JOIN Customers where Orders.CustomerID=Customers.CustomerID;

SQL Group By:
List the number of customers in each country.
select count(EmpID), country from Employees GROUP BY country;

List the number of employees in each country, ordered by the country with the least number of employees first.
select count(EmpID), country from Employees GROUP BY country ORDER BY count(EmpID) ASC;

List the number of employees in each country, ordered by the country with the most employees first.
select count(EmpID), country from Employees GROUP BY country ORDER BY count(EmpID) DESC;

Reference: W3Schools

Leave a Reply

Your email address will not be published. Required fields are marked *