Description

Q1)Display the students’ details (who live in Delhi) and have enrolled in a Java Course.
STUDENT Table
S_ID S_NAME S_ADDRESS S_PHONE S_AGE
S1 RAM DELHI 9455123451 18
S2 RAMESH GURGAON 9652431543 18
S3 SUJIT Chandigarh 9156253131 20
S4 SURESH DELHI 9156768971 18
S5 Ratan Delhi 9567890876 20
Course Table
C_ID C_NAME
C1 DSA
C2 JAVA
C3 MERN
Student_Course Table
S_ID C_ID
S1 C1
S1 C3
S2 C1
S3 C2
S4 C2
S4 C3
S5 C2
Expected Output-
| s_id | s_name | s_address | s_phone | s_age |
| S4 | Suresh | DELHI | 9156768971 | 18 |
| S5 | Ratan | DELHI | 9567890876 | 20 |
Q2)
Movie Table-
id title Director year length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug’s Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
4 Monsters, Inc. Pete Docter 2001 92
5 Civil War Andrew Stanton 2003 107
6 The Incredibles Brad Bird
Movie_Rating Table- 2004 116
movie_id rating domestic_sales international_sales
5 8.2 380843261 555900000
1 7.4 268492764 475066843
3 8 206445654 417277164
2 6.4 191452396 368400000
4 7.9 245852179 239163000
6 8 261441092 370001000
Write the Query for the following condition-
-Show the movie name,domestic sales,international sales for each movie that did better internationally rather than domestically in terms of sales.
-List all the movies by their ratings in descending order.
Q4) Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Return the result table in any order.
The query result format is in the following example.
Input:
Person table:
+————+————–+————–+ | personId | lastName | firstName |
+————-+————-+————–+
| 1 | Wang | Allen | | 2 | Alice | Bob | +————-+————-+————–+ Address table:
+————-+————-+——————–+————-+
| addressId | personId | city | state |
+————-+————-+——————-+————–+ | 1 | 2 | New York City | New York |
| 2 | 3 | San Diego | California | +———–+—————+——————–+————–+
Output:
+————-+————–+——————–+———-+
| firstName | lastName | city | state |
+————-+————–+——————-+———-+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York|
+————-+————–+——————–+———-+