ViewertViewert

SQL Learning on W3SCHOOL AND HACKERRANK platforms

By Katharina · · 13 min read

SQL is Structured Query Language.

SQL lets you create, store, access (query) and manipulate databases.

WHAT CAN SQL DO?

SQL can execute queries from a database.

SQL can retrieve data from a database.

SQL can insert data into a database.

SQL can update data in a database.

SQL can delete data from a database.

SQL can create new databases.

SQL can create new tables in a database.

SQL can create stored procedures in a database.

SQL can create views in a database.

SQL can set permissions, procedures and views in a database.

SQL Data Types for MySQL, SQL Server, and MS Access

  • String (CHAR(size), VARCHAR(size), LONGTEXT)

  • Numeric (INTEGER(size), FLOAT(size, d), DECIMAL(size, d))

  • Date and time (DATE (Format: YYYY-MM-DD), DATETIME(fsp)(YYYY-MM-DD hh:mm:ss))

TYPES OF SQL COMMANDS

  • DATA QUERY (SELECT)

  • DATA DEFINITION (CREATE, ALTER, DROP)

  • DATA MANIPULATION (INSERT, UPDATE, DELETE)

  • DATA CONTROL (GRANT, REVOKE)

RDBMS is Relational Database Managment System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns (fields )and rows (records).

INTERVIEW TASKS

1 RETURN EMPLOYEE RECORD WITH MAX SALARY (HIGHEST PRICE)

SELECT * FROM EMPLOYEE
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE);

OR

SELECT * FROM employee ORDER BY salary DESC LIMIT 1;

2 SELECT HIGHEST SALARY/PRICE FROM EMPLOYEE/PRODUCT TABLE

SELECT MAX(SALARY/PRICE) FROM EMPLOYEE/PRODUCT;

3 SELECT SECOND HIGHEST SALARY FROM EMPLOYEE TABLE

SELECT MAX(SALARY) FROM EMPLOYEE
WHERE SALARY NOT IN (NOT IN MEANS NOT EQUAL) (SELECT MAX(SALARY) FROM EMPLOYEE);

4 SELECT RANGE OF EMPLOYEE BASED ON ID

SELECT * FROM EMPLOYEE
WHERE EMPLOYEE_ID BETWEEN 10 AND 20;

5 RETURN EMPLOYEE NAME, HIGHEST SALARY AND DEPARTMENT from employee and department tables

SELECT employee.NAME, employee,SALARY, department.DEPARTMENT_NAME FROM EMPLOYEES
INNER JOIN department ON (employee.employee_ID = department.DEPARTMENT_NAME)
WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE)

6 RETURN EMPLOYEE NAME, HIGHEST SALARY, DEPARTMENT NAME FOR EACH DEPARTMENT

SELECT e.NAME, e.SALARY, d.DEPARTMENT_NAME from EMPLOYEE e
INNER JOIN DEPARTMENT D ON (E.EMPLOYEE_ID = D.DEPARTMENT_NAME)
WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPARTMENT_ID);

task

select all columns for usa cities and p > 100000

select * from city
where countrycode = 'usa' and population > 100000;

task

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

select name from city 
where countrycode = 'usa' and population > 120000;

task

Query all columns for a city in CITY with the ID 1661.

select * from city
where id = 1661;

task

Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

select * from city
where countrycode = 'jpn';

task

Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.

select name from city
where countrycode = 'jpn';

task

Query a list of CITY names from STATION for cities that have an even (четное) ID number. Print the results in any order, but exclude duplicates from the answer.

SELECT DISTINCT(CITY) FROM STATION WHERE (ID%2)=0;

TASK 1

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

Sample Output

ABC 3
PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with 3 lengths  and . The longest name is 4 PQRS, but there are 3 options for shortest named city. Choose ABC, because it comes first alphabetically.

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC, CITY LIMIT 1;
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY LIMIT 1;

TASK 2

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[a,e,i,o,u]';

OR

SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%';

TASK 3

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

select distinct city from station 
where left(city,1) in ('a','e','i','o','u') 
and right(city, 1) in ('a','e','i','o','u')

OR

select distinct city from station where city regexp "^[aeiou]" and city regexp "[aeiou]$"

OR

simplified by taking out the nested SELECT and just replacing it with another REGEXP.

So we have for mySQL:

SELECT DISTINCT city FROM station WHERE city RLIKE '^[aeiou]' AND city RLIKE '[aeiou]$'

RLIKE is just a synonym for REGEXP for those who don't know.

Also, simplified even more by (credit: rshaghoulian):

SELECT DISTINCT city FROM station WHERE city RLIKE '^[aeiou.*aeiou]$'

TASK 4

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION 
WHERE CITY REGEXP '^[^aeiou]';

OR

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '^[^aeiou]';

TASK 5

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION 
WHERE CITY REGEXP '[^aeiou]$';

OR

SELECT DISTINCT CITY FROM STATION 
WHERE CITY NOT REGEXP '[aeiou]$';

TASK 6

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT RLIKE '^[AEIOU]' OR CITY NOT RLIKE '[AEIOU]$';

OR

SELECT DISTINCT CITY FROM STATION
WHERE CITY RLIKE '^[^AEIOU]' OR CITY RLIKE '[^AEIOU]$';

TASK 7

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION 
WHERE CITY REGEXP '^[^AEIOU]' AND CITY REGEXP '[^AEIOU]$';

OR

SELECT DISTINCT CITY FROM STATION 
WHERE CITY NOT REGEXP '^[AEIOU]' AND CITY NOT REGEXP '[AEIOU]$';

TASK 8

Query the Name of any student in STUDENTS who scored higher than  Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

SELECT NAME FROM STUDENTS
WHERE MARKS > 75 
ORDER BY RIGHT (NAME, 3), ID;

TASK 9

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

SELECT name FROM Employee
ORDER BY name;

TASK 10

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than 2000  per month who have been employees for less than 10 months. Sort your result by ascending employee_id.

SELECT name FROM Employee
WHERE salary > 2000 AND months < 10
ORDER BY employee_id;

TASK 11

Query the total population of all cities in CITY where District is California.

SELECT SUM(POPULATION) FROM CITY
WHERE DISTRICT = 'California';

TASK 12

Query the average population of all cities in CITY where District is California.

SELECT AVG(POPULATiON) FROM CITY
WHERE DISTRICT = 'California';

TASK 13

Query the average population for all cities in CITY, rounded down to the nearest integer.

SELECT ROUND(AVG(POPULATION), 0) FROM CITY;

TASK 14

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN. (ANSWER RIGHT FOR ORACLE, MYSQL AND MS SQL SERVER )

SELECT SUM(POPULATION) FROM CITY
WHERE COUNTRYCODE = 'JPN';

TASK 15

Query the difference between the maximum and minimum populations in CITY. (ANSWER IS RIGHT FOR ORACLE, MYSQL, MS SQL SERVER)

SELECT MAX(POPULATION) - MIN(POPULATION) AS JOKE FROM CITY;

TASK 16

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her 0 keyboard's  key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeroes removed), and the actual average salary.

Write a query calculating the amount of error (i.e.:  average monthly salaries), and round it up to the next integer.

RIGHT ANSWER FOR ORACLE AND MYsql

SELECT CEIL(AVG(Salary)-AVG(REPLACE(Salary,'0',''))) FROM EMPLOYEES;

TASK 17

We define an employee's total earnings to be their monthly  SALARY * MONTHS worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

select (salary * months)as earnings ,count(salary * months) from employee group by earnings order by earnings desc limit 1;

OR

select (salary * months)as earnings ,count(*) from employee group by earnings order by earnings desc limit 1;

FOR ORACLE

SELECT * FROM (SELECT MAX(MONTHS*SALARY), COUNT(EMPLOYEE_ID) FROM EMPLOYEE GROUP BY MONTHS*SALARY ORDER BY MONTHS*SALARY DESC) WHERE ROWNUM = 1;

EXPECTED OUTPUT

108064 7

TASK 18

Query the following two values from the STATION table:

  1. The sum of all values in LAT_N rounded to a scale 2 of  decimal places.

  2. The sum of all values in LONG_W rounded to a scale 2 of  decimal places.

SELECT ROUND(SUM(LAT_N), 2) AS lat, ROUND(SUM(LONG_w), 2) AS lon FROM STATION;

works properly for MySQL and Oracle.

TASK 19

Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880  and less 137.2345 than . Truncate your answer to 4  decimal places.

SELECT ROUND(SUM(LAT_N), 4) FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;

OR

SELECT TRUNCATE(SUM(LAT_N), 4) FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;

TASK 20

Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345 . Truncate your answer to 4 decimal places.

SELECT ROUND(MAX(LAT_N), 4) FROM STATION
WHERE LAT_N < 137.2345;

TASK 21

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 . Round your answer to 4  decimal places.

SELECT ROUND(LONG_W, 4) FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);

TASK 22

Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.

SELECT ROUND(LONG_W, 4) FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

TASK 23

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

SELECT CITY.NAME FROM CITY 
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE CONTINENT = 'AFRICA';

TASK 24

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT;

TASK 25

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

SELECT SUM(CITY.POPULATION) FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE CONTINENT = "ASIA";

TASK 26

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

  • Equilateral: It's a triangle with 3 sides of equal length.

  • Isosceles: It's a triangle with 3 sides of equal length.

  • Scalene: It's a triangle with 3 sides of differing lengths.

  • Not A Triangle: The given values of A, B, and C don't form a triangle.

SELECT CASE 
    WHEN A + B > C AND A + C > B AND B + C > A THEN
        CASE 
            WHEN A = B AND B = C  THEN 'Equilateral'
            WHEN A = B OR A = C OR B = C THEN 'Isosceles'
            ELSE 'Scalene'
        END
ELSE 'Not A Triangle' 
END  
FROM TRIANGLES;

the answer works for Oracle, MySQL, ms sql server

TASK 27

P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* * * * * 
* * * * 
* * * 
* * 
*

Write a query to print the pattern P(20).

ANSWER FOR MS SQL SERVER

DECLARE @i INT = 20
WHILE (@i > 0) 
BEGIN
   PRINT REPLICATE('* ', @i) 
   SET @i = @i - 1
END

OR

set @number = 21;
select repeat('* ', @number := @number - 1) from information_schema.tables;

TASK 28

P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* 
* * 
* * * 
* * * * 
* * * * *

Write a query to print the pattern P(20).

ANSWER FOR MS SQL SERVER

DECLARE @i int = 1
while (@i <= 20)
begin
print replicate ('* ', @i)
set @i = @i + 1
end;

or

DECLARE @i int = 1
while (@i < 21)
begin
print replicate ('* ', @i)
set @i = @i + 1
end;

TASK 29

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

ANSWER

select if(grades.grade > 7, students.name, "NULL") as Names, grades.grade, students.marks from students
INNER JOIN grades ON students.marks BETWEEN grades.Min_Mark AND grades.Max_Mark
ORDER BY grades.grade DESC, students.name, students.marks;

OR

SELECT
    CASE
        WHEN GRADES.GRADE > 7
        THEN STUDENTS.NAME
        WHEN GRADES.GRADE <=7
        THEN NULL
    END, GRADES.GRADE, STUDENTS.MARKS FROM STUDENTS
INNER JOIN GRADES ON STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
ORDER BY GRADES.GRADE DESC, STUDENTS.NAME, STUDENTS.MARKS;

OR

SELECT STUDENTS.NAME, GRADES.GRADE, STUDENTS.MARKS FROM STUDENTS
INNER JOIN GRADES ON STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
WHERE GRADES.GRADE > 7
ORDER BY GRADES.GRADE DESC, STUDENTS.NAME;

SELECT NULL, GRADES.GRADE, STUDENTS.MARKS FROM STUDENTS
INNER JOIN GRADES ON STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
WHERE GRADES.GRADE <=7
ORDER BY GRADES.GRADE DESC, STUDENTS.MARKS;

TASK 30

SELECT
    ROUND(SQRT(
        POWER(MAX(LAT_N)  - MIN(LAT_N),  2)
      + POWER(MAX(LONG_W) - MIN(LONG_W), 2)
    ), 4)
FROM 
    STATION;

TASK 31