Q1.
Query all columns for all American cities in the CITY table with populations larger than 100000.
The CountryCode for America is USA.
The CITY table is described as follows:
/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid
error.
*/
select ID, NAME, COUNTRYCODE, DISTRICT, POPULATION from CITY where POPULATION>100000 and
COUNTRYCODE='USA'
---------------------------------------------------------------------------------------
Query the NAME field for all American cities in the CITY table with populations larger than 120000.
The CountryCode for America is USA.
The CITY table is described as follows:
/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid
error.
*/
select NAME from CITY where COUNTRYCODE='USA' and POPULATION>120000
------------------------------------------------------
select * from [dbo].[ORDERS] where cast([row id] as int)% 2=0
Find the difference between the total number of CITY entries in the table and the number of
distinct CITY entries in the table.
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.
For example, if there are three records in the table with CITY values 'New York', 'New York',
'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'. The query returns ,
because .
select count(CITY)-count(distinct CITY) from STATION
----------------------------------------------------------------------------------------------------------
Generate the following two result sets:
1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed
by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For
example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences
in ascending order, and output them in the following format:
3. There are a total of [occupation_count] [occupation]s.
where [occupation_count] is the number of occurrences of an occupation
in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than
one Occupation has the same [occupation_count], they should be ordered alphabetically.
Note: There will be at least two entries in the table for each type of occupation.
Input Format
The OCCUPATIONS table is described as
follows: Occupation will only contain one of the
following values: Doctor, Professor, Singer or Actor.
Sample Input
An OCCUPATIONS table that contains the following records:
Sample Output
Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.
Explanation
The results of the first query are formatted to the problem description's specifications.
The results of the second query are ascendingly ordered first by number of names corresponding to each
profession (), and then alphabetically by profession (, and ).
select concat(Name,'(',substring(Occupation,1,1),')') from OCCUPATIONS order by Name;
select concat('There are a total of ',count(Occupation),' ',lower(Occupation),'s.')
from OCCUPATIONS group by Occupation order by count(Occupation) asc, Occupation asc;
---------------------------------
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 is the parent of N.
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 node:
Root: If node is root node.
Leaf: If node is leaf node.
Inner: If node is neither root nor leaf node.
Sample Input
Sample Output
1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf
Explanation
The Binary Tree below illustrates the sample:
Select N,
case when P is Null then 'Root'
when N in (select P from BST) then 'Inner' --note:P from bst means
internal node; when N is also in P column that means it is internal
else 'Leaf'
end as X
from BST order by N;
---------------------------------------------------
Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this
hierarchy:
Given the table schemas below, write a query to print the company_code, founder name, total number
of lead managers, total number of senior managers, total number of managers, and total number
of employees. Order your output by ascending company_code.
Note:
The tables may contain duplicate records.
The company_code is string, so the sorting should not be numeric. For example, if
the company_codes are C_1, C_2, and C_10, then the ascending company_codes will
be C_1, C_10, and C_2.
Input Format
The following tables contain company data:
Company: The company_code is the code of the company and founder is the founder of the
company.
Lead_Manager: The lead_manager_code is the code of the lead manager, and
the company_code is the code of the working
company.
Senior_Manager: The senior_manager_code is the code of the senior manager,
the lead_manager_code is the code of its lead manager, and the company_code is the code of
the working company.
Manager: The manager_code is the code of the manager, the senior_manager_code is the
code of its senior manager, the lead_manager_code is the code of its lead manager, and
the company_code is the code of the working
company.
Employee: The employee_code is the code of the employee, the manager_code is the code of
its manager, the senior_manager_code is the code of its senior manager,
the lead_manager_code is the code of its lead manager, and the company_code is the code of
the working company.
Sample Input
Company Table: Lead_Manager Table
: Senior_Manager Table
: Manager Table
Employee Table:
Sample Output
C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2
Explanation
In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1.
There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under
manager M1.
In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are
two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2,
and another employee, E4, under manager, M3.
SELECT
DISTINCT Company.company_code,
Company.founder,
COUNT(DISTINCT lead_manager_code),
COUNT(DISTINCT senior_manager_code),
COUNT(DISTINCT manager_code),
COUNT(DISTINCT employee_code)
FROM Employee
INNER JOIN Company ON Company.company_code = Employee.company_code
GROUP BY Company.company_code, Company.founder
ORDER BY Company.company_code;
-------------------------
Consider and to be two points on a 2D plane where are the respective minimum and maximum values
of Northern Latitude (LAT_N) and are the respective minimum and maximum values of Western
Longitude (LONG_W) in STATION.
Query the Euclidean Distance between points and and format your answer to display decimal digits.
Input Format
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.
select cast(sqrt(square(max(LAT_N)-min(LAT_N)) + square(max(LONG_W)-min(LONG_W))) as
decimal (15,4)) from STATION
---------------------
The Report
110 more points to get your next star!
Rank: 433113|Points: 340/450
Sql
Problem
Submissions
Leaderboard
Discussions
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.
Write a query to help Eve.
Sample Input
Sample Output
Maria 10 99
Jane 9 81
Julia 9 88
Scarlet 8 78
NULL 7 63
NULL 7 68
Note
Print "NULL" as the name if the grade is less than 8.
Explanation
Consider the following table with the grades assigned to the students:
So, the following students got 8, 9 or 10 grades:
Maria (grade 10)
Jane (grade 9)
Julia (grade 9)
Scarlet (grade 8)
select --ID,
case when Grade>=8 then Name
else NULL
end as Name,
Grade, Marks --, Min_Mark, Max_Mark
from [dbo].[Students] as s join [dbo].[Grades] as g on s.Marks between g.Min_Mark and
g.Max_Mark --note the on clause
order by Grade desc, Name asc, Marks asc
--Note the importance od Name column -- when Name is null(grade 1-7) then order by
marks
--------------------------------------------------------------------------------------
----------------
Top Competitors
110 more points to get your next star!
Rank: 433115|Points: 340/450
Sql
Problem
Submissions
Leaderboard
Discussions
Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write
a query to print the respective hacker_id and name of hackers who achieved full scores for more than
one challenge. Order your output in descending order by the total number of challenges in which the
hacker earned a full score. If more than one hacker received full scores in same number of challenges, then
sort them by ascending hacker_id.
Input Format
The following tables contain contest data:
Hackers: The hacker_id is the id of the hacker, and name is the name of the
hacker.
Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score
of the challenge for the difficulty level.
Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker
who created the challenge, and difficulty_level is the level of difficulty of the
challenge.
Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker
who made the submission, challenge_id is the id of the challenge that the submission belongs
to, and score is the score of the submission.
Sample Input
Hackers Table: Difficulty Table:
Challenges Table:
Submissions Table:
Sample Output
90411 Joe
Explanation
Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.
Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.
Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.
Only hacker 90411 managed to earn a full score for more than one challenge, so we print the their hacker_id and name as space-separated values.
