1. Query all cokimns forall American ets inthe CITY table with populations larger than 100000. “The CountryCode for America is USA. ‘The EIT table is described a5 follows: ony 10 ‘NUMBER NAME VARCHAR (17) COUNTRYCODE VARCHAR2(3) DISTRICT VARCHAR2(20) POPULATION NUMBER 2. Generate the following two rasult sts: ‘LQuery an aiphaberically ordered lst of all names in OCCUPATIONS, immediately followed by the firstletter of each profession asa parenthetical (Le: enclosed in parentheses). For example: AnfctorName(A}, ADoctoxName(0), AProfessorlame(, and ASingerNametS) [Link] the number of ocurtences af each occupation in OCCUPATIONS. Sort the occurrences in ascending over, land output them inthe folowing format: “There are a total of foccupation_ count occupation). “where [occupation count] is the number of occurrences ofan occupation in OCCUPATIONS and [occupations ‘the lowercase occupation name. I more than one Occupation has the same [occupation count], they should be ‘ordered alphabetically. Note: There wil beat least two entries inthe table fr each type of occupation Input Format ‘The OCCUPATIONS table is described a follows: the following values: Doctor, Professor, Singer or Actor eony beater Sample Input re ‘noe ‘An OCCUPATIONS table that contains the following records:Sample Output shel?) Chviseeen) zane(0) Jenny) sult) etiy) Mariel) Meera(S} Priya(s) Sementha(0) ‘Thote are a total of 2 doctors. Thete ate a total of 2 singers. ‘There are a total of 3 actors. “There ae a total of 3 professors. Explanation “The results ofthe first query are formatted tothe prablem description’ speciation, ‘The results ofthe second query are ascendingly ordered first by number of names corresponding to each protession (2<-2<+3¢-3), and then alphabetiallyby profession (doctor<= singer, and actor<=professor). 4. Employees With Missing Information, Table: Employees ype | Tint | | varehae | employee id isthe primary key for this able. ach row ofthis table indicates the name ofthe employee whose ID i employee. id | column Narne Table: Solaries | column Name [Type | Llemployee id | int | [salary Lint | employee. id isthe primary hey for this able Each row ofthis table indicates the salary ofthe employee whose IDis employee id.‘write an SQL query to report the 10s of all the employees with missing information The information ofan employee's missing if: ‘The employee's name is missing, oF ‘The employee's salary is missing Return the result table ordered by employee. in ascending order. ‘The query cesuit formats in the following example. Sten Storie , ;, ; ‘Belanation: coe iso . [moe tsey Etmloyees, 2,4 and ate working this company. Taradrccfengoyessmsing Simon 131 7n | Mociy ofempiye2 sree tae tangle iu be oomee ns Explanation: 612020 -06-30 15:06:07 | 612021 -08-21 14:06:06 | | 12019 03.07 00:18:15 | | 812020 -020105:10:53 | 812020 -12.3000:46:50 | 1212020 -01-1602:49:50 | 1212019 -08-2507:59:08 | | 14] 2019-07-14 09:00:00 | | 14] 2021-01-06 11:59:59 | User 6 logged nto their account 3 times but only once in 2020, 0 we include this login in the result table. User 8 loggedinto their account 2 times in 2020, once in Februsty and once in December. We inclide only the latest one (December inthe result table User 2 logged inte their account 2 times but only ance in 2020, ‘0 we include this login in the result table. User 14 did not login in 2020, so we do not include them in the recut table | userid | laststamp | | 61 2020-05-30 15:06:07 | | 812020, -12-3000:46:50 | | 212020 -01-16.02:9:50 |5. Customer Who Visited but Did Not Miake Any Transactions Toble: Visits | column Name | Type | 1 vistid [ine | J oustomerid [int | ssid isthe primary key for this table, This table contains information about the customers who visited the mall ‘able: Tancactions I column Name | Toe | | wonsaction id | int| | usta Lint | | amount [int | ‘rancacton_id ithe primary ke fr this table. This table contains information about the transactions made during the visit i write a Sal query to find the IDs ofthe users who visited without making any transactions and the number of times they made these types of ists Return the result table sorted in any order. The query result format isin the following example. example Input vets out [wie Tome | Testomer i | coupons | | Ise 121 ! 13011! | 198/11 ! ae \ cclraton Js! Cintomer with id= 23 visited the ml once and made one twansaction during the vist with id= 22. Customer with id= 9 visited the mall onee and made one transaction during the vist with Customer with id= 30 visited the mall once and did not make Sy ansnesone. 13181 300 asomerwithd=54 sted the mal thee times. Dring? 13 || 2001 vi they id not ake any traneaone, nd uring one jaz [2/101 they made tersactons (1312 19701 stoma h i= ede aoe an eo ake ‘AS we can 529, users with IDs 30 and 96 visited the mall one time without making any transactions. Abo, user $4 visited the mall twice end didnot make any transactions.6. Dally Leads and Partners “ble: Datlysales | Column Name | Type | | date_id | date | | make_neme | varchar | [lead id Line | | partner int This table does not have a primary key. This table contains the date andthe narne ofthe product sold and the Ds ofthe lead and partner it was sold to. “The name consists of only lowercase English eters \arite an SAL query that wil, for each date id anc make_name, return the numberof distinct leas and distinct partner id's ‘Return the result table in any order ‘The query cesult format sin the following example Bample 1 Apt DailSsles table: | date id | make_name | lead id | partner id | | 2020128 | toyor. | | 2020-128 | toyota. | | 2020328 | toyota. | 1 I | 2020227 | toyors | 2020227 | toyers | 2020128 | honda Ewoanation: eee ee For 20205128, toyota gets leads = 0,1] and partners =, 1,2) 11121 i231 302027 | honda while hondo gets leods= [1,2] and partners = 1, 2 [Joroaz? thenda [212 | For 2020-127, toyota gets leads = [0] and partners = [2,21 | 2020-127 | honda |211| while hand gets le Output I date-id | make_name | unique Jeads | uriqve_partners | (0,1, 2] and partners 1, 2. | 2020328 | ovo | | 2020-2-7 | toyots | | 2020328 honds | | 2020:12-7 | honda |7. Patients With a Condition Table: Patients I Column Name | Type | | pation id Lint | | patientname | varchar | [conditions| varchar | patients the primary key fr this table. ‘conditions contain O or more code separated by spaces. ‘This table contains information ofthe patients in the hospital Write an SQL query to report the patient id, patient_name and coneltions ofthe patients who have Type Diabetes. Type I Diabetes always starts with DIABL prefix. Return the result table in any order. ‘The query result format sin the following example. fsampl Input: Foren table: [pain patente | condos | Ti oan | evcouem | 21 Alice | | | 3 Bob | DIaBi00 MYoP | | 4 | George | ACNE DIAB100 | 1/51 Alain | 18201 | Output: | patient i | [Link] | conditions | | 3 | Bob | DIaBi00 MOP | | 41 George | ACNE D1A8100 | Explanation: Bob and George both have a condition that starts with O1ABIL£8. Write a query identifying the type of each recordin the TRIANGLES table using its three side lengths. Output one ofthe folowing statements for each record in the table “quilter's triangle vith sides of equal length. ‘tsoseeles: t's triangle with sides of equal lenath *Sealene: I's a viangle with sides of dtferng lengths ‘Not A Tangle: The given values of , B, and C don't form a riangle Input Format ‘The TRIANGLES table is described 28 fellows: cobma ne Exch row inthe table denotes the lengths of each ofa triange's tree sides. Sampletnpst zs ‘Semple Output leoeceles js js Equilateral eon ‘Sealene . «oe Not Tiangle 9. ula just finished conducting coding contest, snd she needs your help assembling the leaderboard! Write @ query to print the respective hacke_id and name of hackers sho achieved full scores for mere than ane challenge. Order ‘your output in descending order by the total number of challenges in which the hacker earned a full score. tf more than one hacker received full scores in same numberof challenges, then sort them by ascending hacker_id Input Format ‘The following tables contain contest data: ‘Hlackes: The hacker ids thei ofthe hacker, and name the name of the hacker, Dificuty: The diffcut_ level isthe level of fcuty ofthe challenge, and score isthe seore ofthe challenge for the dificult level coe Te Challenges: The challenge. id's theid ofthe challenge, the hacker idistheid ofthe engage hhacker wo created the challenge, and cifficully_ level's the level of difculty ofthe id challenge. ee“Submissions: The submission ids thei ofthe submission, hacker ids theid ofthe hacker who made the submission, chllenge_id isthe id ofthe challenge thatthe submision belongs to, and score isthe score ofthe submission. emt somos ene i we teoerJa_ | vom ely Tale: Sample ost Se ma Hockers Table So : Challenges Table: = [==] = Submizsion Table: = a a oe Sample Output = i 90 JoeChallenges Table Submissions Table: 10. You are given two tables: Students and Grades. Students con Grades contains the following ta: ‘Sample Output 90811 Joe teenage three columns D, Nome and Marks. Kotty gives Eve a task to generate a report containing tree columns: Nome, Grade and Mork Ketty doesnt want the NAMES of those students who received a grode lower than 8. ‘The report must be in descending order by grade ~ i.e higher rades are entered First. there is more than one student wth the same grade (8-10) assigned to them, order those particl ‘students by their name alphabetically Finally ifthe grade is lower than 8, use "NULL" as their name and list them by their srades in dosconding order. f theres more than one student ith the same grade (17) assigned to them, order those particular students by their marks in ascending order. ‘rice a query to help Eve.Sermpletnput name | aks ‘Sample Output Maria 10 99 Jane 9 Bt sia 9 8 Scarlet 878 NULL 763 NULL 68 Note Print "NULL" asthe nameif the arade's less than 8 4. Table: Losing [column Name | Type | | userid Tint | [time_stamp | datetime | (user-id, time_stamp] isthe primary key fr this able. Explanation Consider the fllowing table with the grades assigned tothe students: So, the following students got , or 10 grades: Moria (grade 10) ‘lane (grade 5) “aula (grade 9) sScartet (rade 8) neh row contains information about the login time forthe user with ID useri ‘rite an SOL query to report the latest login forall users in the year 2020. Do not include the users who didnot login in 2020, Return the result table in “The query result formst order.