03 _SQL Practice Questions

15 February 2021 13:46

To joins or where classes more efficients make sure you cast the variables into the same varialbes. For example, A join B on cast([Link] as nvarchar2(40)) = cast([Link] as nvarchar2(40))

when creating temporary table. Note there is no comma

WITH data AS(query)
select **

rownum: it is a where clause so it cannot be used like limit after groupby or order functions.
select .. from..where rownum <2 order by .. is not the same as select ..from..order by..limit. The rownum is first applied and then order by is applied. Rownum comes straight after where not and, or etc.

You cannot reference variable created in query . For example, the following won't work
Select months*salary as total_earnings
Where total_earnings > 2000;
Instead
Select months*salary as total_earnings
Where months*salary > 2000;

GROUP BY: Whichever variables are in the select they also need to be in the GROUP BY statement as well

JOINING tables through select from: When no common id exist to join tables use where to join table based on some comparison
select case when grade < 8 then NULL else name end name, grade, marks
from students, grades
where marks between min_mark and max_mark
order by grade desc, name, marks asc;
Column with spaces i.e last name you can use bracket. For example, select table.[last name] now if there is no space it won't work i.e table.[sex] will fail.

UNION keeps unique values

UNION ALL keeps everything

INNER JOIN
FULL OUTER JOIN
LEFT JOIN
RIGHT JOIN
CASE WHEN x>y THEN 'myvalue' ELSE 'othervalue' END name_it
x BETWEEN a AND b : this means greater than a and small than b, excluding a and b
You can add further when's and they will function as ifelse
round
floor
ceil
Avg
Distinct / unique
SUBSTR : ex. SUBSTR('manger', 2, 3) the 2 indicates the position 'a' and 3 the total length of the output
string. The output is 'ang'.
concat('a', 'b') = 'ab', only take two arguments
replace(feature, '|' , '1')

to_char()
to_number()
cast(num AS nvarchar2(40))
cast(string AS NUMBER)

max(feature) : used with groupby, unlike pytKihon you cannot compare two values in it
Min(feature) : used with groupby, unlike python you cannot compare two values in it
greatest(feature1, feature1) : this returns the max value between the two
least(feature1, feature1) : this returns the min value between the two

Group by
Order by

In
Not in
And
Or

IS NOT : often used in where clause

TO_DATE : to_date('Thursday 01/01/2009','DAY MM/DD/YYYY')
ADD_MONTHS : ADD_MONTHS(date_field, 1)
NEXT_DAY : When is the next monday, NEXT_DAY(date_field, 'monday')
LAST_DAY : last day of the month LAST_DAY(to_date(hiredate, 'dd-mm-yy'))
DENSE_RANK() : rank() over (partition by job order by empno)
GREATEST : maximum of list of numbers GREATEST(2.718, 10, 5) you get 10
LEAST : opposite of greatest
ROWNUM : used in the where clause it could be ROWNUM = 1 or ROWNUM < n.
ROW_NUMBER () : use it with OVER to get row numbers.
SIGN : returns 1 or -1 for positive and negative numbers respectively
SQRT : square root returns a columns
STDDEV : returns on value for the columns
lpad or rpad: pads a number to the left ex. lpad('job', 5,'*') gets you '**job'
NVL : if value is null it will repalce it with given value ex. select NVL(comm, 9999) from emp; note that the given value sould be the same datatype.
NULLIF : Converts values to null when condition is met. ex. nullif(job, 'MANAGER') will make every value of job column null if it is 'MANAGER'.
LAG () : ex. lag(job, 2) over (order by empno). Create a new column and moves everything by 2 rows down from job columns.
LEAD () : Does the opposite of LAG.
NTILE () : ex. ntile(2) over (order by job) Creates a new columns based on order by job splits it into 2 quantiles i.e. The first half of the column will contain 1 and the second half 2.
MEDIAN,
MOD,
POWER(2, 3) = 8,
ROUND,
SOUNDEX

[Link] SELECT DISTINCT(CITY) EASY

challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen FROM STATION
WHERE MOD(ID, 2)=0;

[Link] SELECT COUNT(CITY) - COUNT(DISTINCT(CITY)) FROM STATION; EASY

[Link] SELECT * FROM (SELECT CITY, LENGTH(CITY) EASY
Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: FROM STATION
number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first ORDER BY LENGTH(CITY) DESC)
when ordered alphabetically. WHERE ROWNUM=1
UNION
SELECT * FROM (SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY), CITY ASC)
WHERE ROWNUM=1;
[Link] SELECT DISTINCT(CITY) EASY
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. FROM STATION
Your result cannot contain duplicates. WHERE LOWER(SUBSTR(CITY, -1, 1)) IN ('a', 'e', 'i', 'o', 'u')
AND LOWER(SUBSTR(CITY, 1, 1)) IN ('a', 'e', 'i', 'o', 'u');
Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates. SELECT DISTINCT(CITY) EASY
FROM STATION
WHERE LOWER(SUBSTR(CITY, 1, 1)) NOT IN ('a', 'e', 'i', 'o', 'u');
[Link] SELECT NAME EASY
Query the Name of any student in STUDENTS who scored higher than 75 Marks. Order your output by the last three FROM STUDENTS
characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, WHERE MARKS > 75
Robby, etc.), secondary sort them by ascending ID. ORDER BY SUBSTR(NAME, -3), ID ASC;
[Link] SELECT CASE EASY
WHEN A + B > C AND B + C > A AND A + C > B THEN
CASE
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
ELSE 'Scalene'
END
ELSE 'Not A Triangle'
END
FROM TRIANGLES;
[Link] SELECT CEIL(AVG(Salary) - AVG(REPLACE(Salary, '0', ''))) EASY
Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not FROM EMPLOYEES;
realize her 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.
[Link] SELECT * EASY
FROM (SELECT (months*salary), COUNT(*)
FROM EMPLOYEE
GROUP BY (months*salary)
ORDER BY (months*salary) DESC)
WHERE ROWNUM = 1;

Or

select months*salary, count(*)

from employee
where months*salary = (select max(months*salary) from employee)
group by months*salary;
[Link] SELECT ROUND(LONG_W, 4) EASY
FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N)
FROM STATION
WHERE LAT_N < 137.2345);
[Link] SELECT ROUND(MAX(LAT_N)-MIN(LAT_N)+MAX(LONG_W)-MIN(LONG_W), 4) EASY
challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen FROM STATION;
[Link] SELECT ROUND(SQRT(POWER(MAX(LAT_N)-MIN(LAT_N), 2)+POWER(MAX(LONG_W)- EASY
challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen MIN(LONG_W), 2)), 4)
FROM STATION;
[Link] SELECT [Link] EASY
FROM COUNTRY INNER JOIN CITY ON [Link] = [Link]
WHERE [Link] = 'Africa';
[Link] SELECT [Link], FLOOR(AVG([Link])) Med
Given the CITY and COUNTRY tables, query the names of all the continents ([Link]) and their respective FROM COUNTRY INNER JOIN CITY ON [Link] = [Link]
average city populations ([Link]) rounded down to the nearest integer. GROUP BY [Link];

Note: [Link] and [Link] are matching key columns.

[Link] SELECT CASE WHEN [Link] > 7 THEN [Link] ELSE NULL END NAME, MED
Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the [Link], [Link]
NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. FROM GRADES, STUDENTS
higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order WHERE [Link] BETWEEN GRADES.Min_Mark and GRADES.Max_Mark
those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and ORDER BY [Link] DESC, Name ASC, [Link] ASC;
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.
[Link] SELECT H.hacker_id, [Link] MED
Four tabel merge - new technicque FROM Submissions S INNER JOIN Hackers H ON S.hacker_id = H.hacker_id
Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your INNER JOIN Difficulty D ON C.difficulty_level=D.difficulty_level
output in descending order by the total number of challenges in which the hacker earned a full score. If more than one WHERE [Link] = [Link]
hacker received full scores in same number of challenges, then sort them by ascending hacker_id. GROUP BY H.hacker_id, [Link]
HAVING COUNT(C.challenge_id)>1
Aways start with the large table that has more details. For example, submissions and then join the smaller tables that ORDER BY COUNT(C.challenge_id) DESC, H.hacker_id ASC;
contains properties to it.
[Link] Med
New technique
Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each
non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that
Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order
of descending age.

[Link] WITH data AS ( MED

WITH clause - Note to create multiple temp tables: WITH tbl1 AS (select...), tbl2 AS (select ...) select... SELECT H.hacker_id, [Link], COUNT(C.challenge_id) AS cnt
Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total FROM Hackers H JOIN Challenges C ON H.hacker_id=C.hacker_id
number of challenges created by each student. Sort your results by the total number of challenges in descending order. If GROUP BY H.hacker_id, [Link])
more than one student created the same number of challenges, then sort the result by hacker_id. If more than one SELECT *
student created the same number of challenges and the count is less than the maximum number of challenges created, FROM data
then exclude those students from the result. WHERE cnt = (SELECT MAX(cnt) FROM DATA)
OR cnt IN (SELECT cnt FROM data GROUP BY cnt HAVING COUNT(cnt)=1)
ORDER BY cnt DESC, hacker_id;
[Link] WITH Doc AS ( Med
Creating Row numbers for join SELECT Doctor, (ROW_NUMBER() OVER (ORDER BY Doctor)) AS Row_num
Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its FROM(SELECT
corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. CASE WHEN Occupation = 'Doctor' THEN name END Doctor
FROM OCCUPATIONS)),
Note: Print NULL when there are no more names corresponding to an occupation. Prof AS (
SELECT Professor, (ROW_NUMBER() OVER (ORDER BY Professor)) AS Row_num
FROM(SELECT
CASE WHEN Occupation = 'Professor' THEN name END Professor
FROM OCCUPATIONS)),
Sing AS (
SELECT Singer, (ROW_NUMBER() OVER (ORDER BY Singer)) AS Row_num
FROM(SELECT
CASE WHEN Occupation = 'Singer' THEN name END Singer
FROM OCCUPATIONS)),
Acto AS (
SELECT Actor, (ROW_NUMBER() OVER (ORDER BY Actor)) AS Row_num
FROM(SELECT
CASE WHEN Occupation = 'Actor' THEN name END Actor
FROM OCCUPATIONS))
SELECT [Link], [Link], [Link], [Link]
FROM Doc D JOIN Prof P ON D.Row_num = P.Row_num JOIN Sing S ON P.Row_num =
S.Row_num JOIN Acto A ON S.Row_num = A.Row_num
WHERE LENGTH([Link]||[Link]||[Link]||[Link]) IS NOT NULL;
[Link] SELECT N, Med
GOOD CASE EXAMPLE CASE
You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P WHEN P IS NULL THEN 'Root'
is the parent of N. WHEN N IN (SELECT P FROM BST) THEN 'Inner'
Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each WHEN N NOT IN P THEN 'Leaf'
node: END TYPE
FROM BST
ORDER BY N ASC;
[Link] SELECT Med
Count distict group by easy example C.company_code,
Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: [Link],
COUNT(DISTINCT(E.lead_manager_code)),
Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, COUNT(DISTINCT(E.senior_manager_code)),
total number of senior managers, total number of managers, and total number of employees. Order your output by COUNT(DISTINCT(E.manager_code)),
ascending company_code. COUNT(DISTINCT(E.employee_code))
FROM Company C JOIN Employee E ON C.company_code = E.company_code
GROUP BY C.company_code, [Link]
ORDER BY C.company_code ASC;
Dual table SELECT
This is a table generated by oracle data base with only one value X. This is useful for calculating constant because every (SELECT COUNT(Col.contest_id) FROM Colleges Col) - (SELECT COUNT(Con.contest_id)
select statement needs a from so the dummy from can be dual. FROM Contests Con)
FROM dual;
[Link] select * HARD
challenge&h_v=zen from (select con.*,
Alternative to groupby (select sum(total_submissions)
Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to from submission_stats s, challenges c, colleges col
print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and where s.challenge_id = c.challenge_id
total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are . and c.college_id = col.college_id
and col.contest_id = con.contest_id) total_submissions,
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds (select sum(total_accepted_submissions)
screening contest. from submission_stats s, challenges c, colleges col
where s.challenge_id = c.challenge_id
and c.college_id = col.college_id
and col.contest_id = con.contest_id) total_accepted_submissions,
(select sum(total_views)
from View_Stats v, challenges c, colleges col
where v.challenge_id = c.challenge_id
and c.college_id = col.college_id
and col.contest_id = con.contest_id) total_views,
(select sum(total_unique_views)
from View_Stats v, challenges c, colleges col
where v.challenge_id = c.challenge_id
and c.college_id = col.college_id
and col.contest_id = con.contest_id) total_unique_views
from contests con)
where total_submissions <> 0
and total_accepted_submissions <> 0
and total_views <> 0
and total_unique_views <> 0
order by contest_id;
[Link] WITH CNT AS (select
challenge&h_v=zen&h_r=next-challenge&h_v=zen Submission_date,
USE of start with - connect by - prior count(distinct Hacker_id) as NumHackers
Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was from SUBMISSIONS
March 15, 2016. group by Submission_date
start with Submission_date='2016-03-01'
Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day connect by
of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If prior to_date(Submission_date,'yyyy-mm-dd') = to_date(Submission_date,'yyyy-mm-
more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print dd')-1
this information for each day of the contest, sorted by the date. and prior Hacker_id=Hacker_id
and to_date(Submission_date,'yyyy-mm-dd') <= to_date('2016-03-15','yyyy-mm-dd'))
select C.Submission_date, [Link], H.hacker_id, [Link]
from CNT C JOIN (select submission_date, hacker_id, name
from (select s.submission_date, h.hacker_id, [Link],
(row_number() over (partition by s.submission_date order by
count(submission_id) desc, h.hacker_id asc)) indx
from submissions s left join hackers h on s.hacker_id = h.hacker_id
group by s.submission_date, h.hacker_id, [Link]
having count(submission_id) > 0)
where indx = 1) H on C.Submission_date = H.submission_date
order by C.Submission_date;
[Link] SELECT Root, End_Date
challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen FROM (SELECT Start_Date, End_Date, CONNECT_BY_ROOT Start_Date AS Root, LEVEL
You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the AS duration FROM Projects WHERE CONNECT_BY_ISLEAF = 1 CONNECT BY PRIOR
 difference between the End_Date and the Start_Date is equal to 1 day for each row in the table. End_Date = Start_Date)
WHERE Root NOT IN (SELECT End_Date FROM Projects) ORDER BY duration, Root;
If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the
total number of different projects completed.

[Link] select [Link]

challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen&h_r=next-challenge&h_v=zen from
You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends students s join packages p on [Link]=[Link]
contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary join friends f on [Link] = [Link]
(offered salary in $ thousands per month). join packages fp on f.friend_id = [Link]
where [Link] < [Link]
Write a query to output the names of those students whose best friends got offered a higher salary than them. Names order by [Link];
must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary
offer.
[Link] select rpad( '*', level*2, ' *' ) stars from dual connect by level <= 20 order by stars desc; easy
Connect by level
P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5): Or you can use:

***** set serveroutput on

**** declare
*** n number(2) := 20;
** i number;
* j number;
Write a query to print the pattern P(20). begin
for i in reverse 1..n
loop
for j in reverse 1..i
loop
dbms_output.put(' saba *');
end loop;
dbms_output.new_line;
end loop;
end;
/

Leetcode
Write a SQL query to get the second highest salary from the Employee table. select ( easy
select Salary
+----+--------+ from (select Salary, rownum as rnum
| Id | Salary | from (select distinct Salary
+----+--------+ from Employee order by Salary desc))
| 1 | 100 | where rnum > 1 and rnum < 3) as "SecondHighestSalary"
| 2 | 200 | from dual;
| 3 | 300 |
+----+--------+ Or the following but group function was not allowed
For example, given the above Employee table, the query should return 200 as the second highest salary. If
there is no second highest salary, then the query should return null. Select max(salary) SecondHighestSalary
From (select * from employee where salary < max(salary));
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

Create table If Not Exists Employee (Id int, Salary int)

Truncate table Employee
insert into Employee (Id, Salary) values ('1', '100')
insert into Employee (Id, Salary) values ('2', '200')
insert into Employee (Id, Salary) values ('3', '300')
Table: Department select id, Easy
sum(case when month = 'Jan' then revenue else null end) jan_revenue,
+---------------+---------+ sum(case when month = 'Feb' then revenue else null end) feb_revenue,
| Column Name | Type | sum(case when month = 'Mar' then revenue else null end) mar_revenue,
+---------------+---------+ sum(case when month = 'Apr' then revenue else null end) apr_revenue,
| id | int | sum(case when month = 'May' then revenue else null end) may_revenue,
| revenue | int | sum(case when month = 'Jun' then revenue else null end) jun_revenue,
| month | varchar | sum(case when month = 'Jul' then revenue else null end) jul_revenue,
+---------------+---------+ sum(case when month = 'Aug' then revenue else null end) aug_revenue,
(id, month) is the primary key of this table. sum(case when month = 'Sep' then revenue else null end) sep_revenue,
The table has information about the revenue of each department per month. sum(case when month = 'Oct' then revenue else null end) oct_revenue,
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]. sum(case when month = 'Nov' then revenue else null end) nov_revenue,
sum(case when month = 'Dec' then revenue else null end) dec_revenue
from department
Write an SQL query to reformat the table such that there is a department id column and a revenue column group by id
for each month. order by id;

The query result format is in the following example:

Department table:
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+

Result table:
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+

Note that the result table has 13 columns (1 for the department id + 12 for the months).

Create table If Not Exists Department (id int, revenue int, month varchar(5))
Truncate table Department
insert into Department (id, revenue, month) values ('1', '8000', 'Jan')
insert into Department (id, revenue, month) values ('2', '9000', 'Jan')
insert into Department (id, revenue, month) values ('3', '10000', 'Feb')
insert into Department (id, revenue, month) values ('1', '7000', 'Feb')
insert into Department (id, revenue, month) values ('1', '6000', 'Mar')
627. Swap salary -- UPDATE, SET, WHERE: where conditions is optional and doesn't always need to be included. The Easy
Table: Salary case condition can work for where.

+-------------+----------+ UPDATE Salary

| Column Name | Type | SET sex = CASE WHEN sex = 'm' then 'f' else 'm' end;
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id is the primary key for this table.
The sex column is ENUM value of type ('m', 'f').
The table contains information about an employee.

Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a
single update statement and no intermediate temp table(s).

Note that you must write a single update statement, DO NOT write any select statement for this
problem.

The query result format is in the following example:

Salary table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
+----+------+-----+--------+

Result table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+----+------+-----+--------+
(1, A) and (2, C) were changed from 'm' to 'f'.
(2, B) and (4, D) were changed from 'f' to 'm'.

create table if not exists salary(id int, name varchar(100), sex char(1), salary int)
Truncate table salary
insert into salary (id, name, sex, salary) values ('1', 'A', 'm', '2500')
insert into salary (id, name, sex, salary) values ('2', 'B', 'f', '1500')
insert into salary (id, name, sex, salary) values ('3', 'C', 'm', '5500')
insert into salary (id, name, sex, salary) values ('4', 'D', 'f', '500')

620. Not Boring Movies select * Easy

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a from cinema
poster indicating the movies’ ratings and descriptions. where mod(id, 2)!=0
Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. and description != 'boring'
Order the result by rating. order by rating desc;

For example, table cinema:

+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
For the example above, the output should be:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
Create table If Not Exists cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1))
Truncate table cinema
insert into cinema (id, movie, description, rating) values ('1', 'War', 'great 3D', '8.9')
insert into cinema (id, movie, description, rating) values ('2', 'Science', 'fiction', '8.5')
insert into cinema (id, movie, description, rating) values ('3', 'irish', 'boring', '6.2')
insert into cinema (id, movie, description, rating) values ('4', 'Ice song', 'Fantacy', '8.6')
insert into cinema (id, movie, description, rating) values ('5', 'House card', 'Interesting', '9.1')

197. Rising Temperature -- good use of date instead of LAG function. Easy
Table: Weather
select [Link]
+---------------+---------+ from weather a, weather b
| Column Name | Type | where [Link] = [Link] -1
+---------------+---------+ and [Link] > [Link];
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature in a certain day.

Write an SQL query to find all dates' id with higher temperature compared to its previous dates
(yesterday).

Return the result table in any order.

The query result format is in the following example:

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
|2 |
|4 |
+----+
In 2015-01-02, temperature was higher than the previous day (10 -> 25).
In 2015-01-04, temperature was higher than the previous day (20 -> 30).

Create table If Not Exists Weather (Id int, RecordDate date, Temperature int)
Truncate table Weather
insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10')
insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25')
insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20')
insert into Weather (Id, RecordDate, Temperature) values ('4', '2015-01-04', '30')

196. Delete Duplicate Emails Mysql solution Easy

MYSQL solution Delete
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails from person
based on its smallest Id. where id not in (select * from (select min(id) from person group by email) as p);

+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@[Link] |
| 2 | bob@[Link] |
| 3 | john@[Link] |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@[Link] |
| 2 | bob@[Link] |
+----+------------------+
Note:

Your output is the whole Person table after executing your sql. Use delete statement.
Truncate table Person
insert into Person (Id, Email) values ('1', 'john@[Link]')
insert into Person (Id, Email) values ('2', 'bob@[Link]')
insert into Person (Id, Email) values ('3', 'john@[Link]')

182. Duplicate Emails select email from person group by email having count(email) >1; Easy
Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email |
+----+---------+
| 1 | a@[Link] |
| 2 | c@[Link] |
| 3 | a@[Link] |
+----+---------+
For example, your query should return the following for the above table:

+---------+
| Email |
+---------+
| a@[Link] |
+---------+
Note: All emails are in lowercase.
184. Department Highest Salary with maxx as (select departmentid, max(salary) maxsalary from employee group by departmentid) Medium
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for select [Link] Department, [Link] employee, [Link]
the department Id. from employee e, department d, maxx m
where [Link] = [Link]
+----+-------+--------+--------------+ and [Link] = [Link]
| Id | Name | Salary | DepartmentId | and [Link] = [Link];
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
The Department table holds all departments of the company.

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
Write a SQL query to find employees who have the highest salary in each of the departments. For the
above tables, your SQL query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the
Sales department.
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')

626. Exchange Seats select Medium

Mary is a teacher in a middle school and she has a table seat storing students' names and their case
corresponding seat ids. when mod(id, 2)=0 then id-1
when id = (select max(id) from seat) then id
The column id is continuous increment. else id+1 end id, student
from seat
Mary wants to change seats for the adjacent students. order by id;

Can you write a SQL query to output the result for Mary?

+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
For the sample input, the output is:

+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
Note:

If the number of students is odd, there is no need to change the last one's seat.
Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

180. Consecutive Numbers select distinct num consecutivenums Medium

Table: Logs from (select num,
lead(num, 1) over (order by id) num1,
+-------------+---------+ lead(num, 2) over (order by id) num2
| Column Name | Type | from logs)
+-------------+---------+ where num=num1 and num=num2;
| id | int |
| num | varchar |
+-------------+---------+
id is the primary key for this table.
Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example:

Logs table:
+----+-----+
| Id | Num |
+----+-----+
|1 |1 |
|2 |1 |
|3 |1 |
|4 |2 |
|5 |1 |
|6 |2 |
|7 |2 |
+----+-----+

Result table:
+-----------------+
| ConsecutiveNums |
+-----------------+
|1 |
+-----------------+
1 is the only number that appears consecutively for at least three times.
Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')

178. Rank Scores with rnk as (select score, row_number() over (order by score desc) rank Medium
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. from (select distinct score from scores))
Note that after a tie, the next ranking number should be the next consecutive integer value. In other select [Link], [Link]
words, there should be no "holes" between ranks. from scores s left join rnk r on [Link] = [Link]
order by [Link];
+----+-------+
| Id | Score | Or alternatively
+----+-------+
| 1 | 3.50 | select score,
| 2 | 3.65 | dense_rank() over (order by score desc) Rank
| 3 | 4.00 | from scores;
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
For example, given the above Scores table, your query should generate the following report (order by
highest score):

+-------+---------+
| score | Rank |
+-------+---------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+---------+
Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an
apostrophe before and after the keyword. For example `Rank`.
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))
Truncate table Scores
insert into Scores (Id, Score) values ('1', '3.5')
insert into Scores (Id, Score) values ('2', '3.65')
insert into Scores (Id, Score) values ('3', '4.0')
insert into Scores (Id, Score) values ('4', '3.85')
insert into Scores (Id, Score) values ('5', '4.0')
insert into Scores (Id, Score) values ('6', '3.65')

177. Nth Highest Salary CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS Medium
Write a SQL query to get the nth highest salary from the Employee table. result NUMBER;
BEGIN
+----+--------+ /* Write your PL/SQL query statement below */
| Id | Salary | select max(salary) into result
+----+--------+ from
| 1 | 100 | (select salary, dense_rank() over (order by salary desc) rank from employee)
| 2 | 200 | where rank = N;
| 3 | 300 |
+----+--------+ RETURN result;
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth END;
highest salary, then the query should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+

For further questions see:

[Link]