sql

0
38

q1)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.
ans:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1;
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY LIMIT 1;

q2)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.
ans:
Declare @Small int
Declare @Large int
select @Small = Min(Len(City)) from Station
select @Large = Max(Len(City)) from Station
select Top 1 City as SmallestCityName,Len(City) as Minimumlength from Station where Len(City) = @Small Order by City Asc
select Top 1 City as LargestCityName,Len(City) as MaximumLength from Station where Len(City) = @Large Order by City Asc

— When we 1st character = [aeiou]%
—-When we last character = %[aeiou]
— and for both 1st and last=[aeiou]%[aeiou]

q3)Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION.
Your result cannot contain duplicates.
ans:
SELECT DISTINCT(CITY) FROM STATION WHERE CITY LIKE ‘[AEOIU]%’;

q4)Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION.
Your result cannot contain duplicates.
ans:SELECT DISTINCT(CITY) FROM STATION WHERE CITY LIKE ‘%[AEOIU]’;

q5)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.
ans:
SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE ‘[aeiou]%[aeiou]’;

q6)Query the list of CITY names from STATION that do not start with vowels.
Your result cannot contain duplicates.
ans:
SELECT DISTINCT CITY
FROM STATION
WHERE CITY not LIKE ‘[aeiou]%’;

q7)Query the list of CITY names from STATION that do not end with vowels.
Your result cannot contain duplicates.
ans:
SELECT DISTINCT CITY
FROM STATION
WHERE CITY not LIKE ‘%[aeiou]’;

q8)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.
ans:
SELECT DISTINCT CITY
FROM STATION
WHERE CITY not LIKE ‘[aeiou]%[aeiou]’;

q9)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.
ans:
SELECT DISTINCT city FROM station WHERE city NOT LIKE ‘[AEIOU]%’ AND city NOT LIKE ‘%[aeiou]’;

— Slicing and asc to any attr eg: ID
Query the Name of any student in STUDENTS who scored higher than 75 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.
ans:
SELECT Name FROM STUDENTS WHERE Marks > 75 ORDER BY RIGHT(Name, 3), ID;

—Order by
q1)Write a query that prints a list of employee names (i.e.: the name attribute) from
the Employee table in alphabetical order.
ans:
select name from employee
order by name asc

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

q3)You are given a table, Functions, containing two columns: X and Y….. (refer link for whole ques and ans).
https://nifannn.github.io/2017/10/24/SQL-Notes-Hackerrank-Symmetric-Pairs/
ans:
SELECT f1.X, f1.Y FROM Functions AS f1
WHERE f1.X = f1.Y AND
(SELECT COUNT(*) FROM Functions WHERE X = f1.X AND Y = f1.X) > 1
UNION
SELECT f1.X, f1.Y FROM Functions AS f1, Functions AS f2
WHERE f1.X <> f1.Y AND f1.X = f2.Y AND f1.Y = f2.X AND f1.X < f2.X
ORDER BY X;

q4)Samantha ….?
https://nifannn.github.io/2017/10/24/SQL-Notes-Hackerrank-Interviews/
ans:
SELECT con.contest_id, con.hacker_id, con.name,
SUM(sg.total_submissions), SUM(sg.total_accepted_submissions),
SUM(vg.total_views), SUM(vg.total_unique_views)
FROM Contests AS con
JOIN Colleges AS col ON con.contest_id = col.contest_id
JOIN Challenges AS cha ON cha.college_id = col.college_id
LEFT JOIN
(SELECT ss.challenge_id, SUM(ss.total_submissions) AS total_submissions, SUM(ss.total_accepted_submissions) AS total_accepted_submissions FROM Submission_Stats AS ss GROUP BY ss.challenge_id) AS sg
ON cha.challenge_id = sg.challenge_id
LEFT JOIN
(SELECT vs.challenge_id, SUM(vs.total_views) AS total_views, SUM(vs.total_unique_views) AS total_unique_views
FROM View_Stats AS vs GROUP BY vs.challenge_id) AS vg
ON cha.challenge_id = vg.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
HAVING SUM(sg.total_submissions) +
SUM(sg.total_accepted_submissions) +
SUM(vg.total_views) +
SUM(vg.total_unique_views) > 0
ORDER BY con.contest_id;

LEAVE A REPLY

Please enter your comment!
Please enter your name here