Advertisements
Advertisements
Question
Consider the following tables – Bank_Account and Branch:
Table: Bank_Account
| ACode | Name | Type |
| A01 | Amrita | Savings |
| A02 | Parthodas | Current |
| A03 | Miraben | Current |
Table: Branch
| ACode | City |
| A01 | Delhi |
| A02 | Mumbai |
| A01 | Nagpur |
What will be the output of the following statement?
SELECT * FROM Bank_Account NATURAL JOIN Branch;
Advertisements
Solution
| ACode | Name | Type | City |
| A01 | Amrita | Savings | Delhi |
| A01 | Amrita | Savings | Nagpur |
| A02 | Parthodas | Current | Mumbai |
APPEARS IN
RELATED QUESTIONS
Which of the following is not a comparison query?
Which function is used with the ORDER BY clause to custom sort order?
Consider the following SQL.
SELECT ______ FROM Employee WHERE Dept= 'Printing';
Which of the following should be used to find the mean of the salary?
Which of the following is not true for SQL?
Which of the following is not a constraint?
Activity table is related to student table and every student has been asked to enroll for minimum 2 activities. While checking the records select student id from activity; lots of duplicate entries were found as students are enrolling for more than one activity. It is becoming difficult to understand who all have still not enrolled for any activity. We can use ______ clause with student_id to resolve the issue.
Write the output produced by the following SQL statement:
SELECT POW(2, 3);
Consider the following MOVIE table and write the SQL query based on it.
| MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost |
| 001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000 |
| 002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000 |
| 003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000 |
| 004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000 |
| 005 | Telugu_Movie | Action | - | 100000 | - |
| 006 | Punjabi_Movie | Comedy | - | 30500 | - |
List business is done by the movies showing only MovieID, MovieName, and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.
Consider the following MOVIE table and write the SQL query based on it.
| MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost |
| 001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000 |
| 002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000 |
| 003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000 |
| 004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000 |
| 005 | Telugu_Movie | Action | - | 100000 | - |
| 006 | Punjabi_Movie | Comedy | - | 30500 | - |
List the different categories of movies.
Consider the following MOVIE table and write the SQL query based on it.
| MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost |
| 001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000 |
| 002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000 |
| 003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000 |
| 004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000 |
| 005 | Telugu_Movie | Action | - | 100000 | - |
| 006 | Punjabi_Movie | Comedy | - | 30500 | - |
Find the net profit of each movie showing its MovieID, MovieName, and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.
Consider the following MOVIE table and write the SQL query based on it.
| MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost |
| 001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000 |
| 002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000 |
| 003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000 |
| 004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000 |
| 005 | Telugu_Movie | Action | - | 100000 | - |
| 006 | Punjabi_Movie | Comedy | - | 30500 | - |
List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
Consider the following MOVIE table and write the SQL query based on it.
| MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost |
| 001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000 |
| 002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000 |
| 003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000 |
| 004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000 |
| 005 | Telugu_Movie | Action | - | 100000 | - |
| 006 | Punjabi_Movie | Comedy | - | 30500 | - |
List details of all movies which fall in the category of comedy or action.
Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:
- Display the MatchID of all those matches where both teams have scored more than 70.
- Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
- Display the MatchID and date of matches played by Team 1 and won by it.
- Display the MatchID of matches played by Team 2 and not won by it.
- Change the name of the relation TEAM to T_DATA. Also, change the attributes TeamID and TeamName to T_ID and T_NAME respectively.
Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:
Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:
Display the MatchID and date of matches played by Team 1 and won by it.
Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:
Display the MatchID of matches played by Team 2 and not won by it.
The SELECT statement when combined with the ______ clause returns records without repetition.
______ clause is used with a SELECT statement to display data in a sorted form with respect to a specified column.
Write the outputs of the SQL queries (i) to (iv) based on the relations COMPUTER and SALES given below:
| Table: COMPUTER | ||||
| PROD_ID | PROD_NAME | PRICE | COMPANY | TYPE |
| P001 | MOUSE | 200 | LOGITECH | INPUT |
| P002 | LASER PRINTER | 4000 | CANON | OUTPUT |
| P003 | KEYBOARD | 500 | LOGITECH | INPUT |
| P004 | JOYSTICK | 1000 | IBALL | INPUT |
| P005 | SPEAKER | 1200 | CREATIVE | OUTPUT |
| P006 | DESKET PRINTER | 4300 | CANON | OUTPUT |
| Table: SALES | ||
| PROD_ID | QTY_SOLD | QUARTER |
| P002 | 4 | 1 |
| P003 | 2 | 2 |
| P001 | 3 | 2 |
| P004 | 2 | 1 |
SELECT MIN(PRICE), MAX(PRICE) FROM COMPUTER;SELECT COMPANY, COUNT(*) FROM COMPUTER GROUP BY COMPANY HAVING COUNT(COMPANY) > 1;SELECT PROD_NAME, QTY_SOLD FROM COMPUTER C, SALES S WHERE C.PROD_ID=S.PROD_ID AND TYPE = 'INPUT';SELECT PROD_NAME, COMPANY, QUARTER FROM COMPUTER C, SALES S WHERE C.PROD_ID=S.PROD_ID;
Consider the table CLUB given below and write the output of the SQL queries that follow.
| CID | CNAME | AGE | GENDER | SPORTS | PAY | DOAPP |
| 5246 | AMRITA | 35 | FEMALE | CHESS | 900 | 2006- 03-27 |
| 4687 | SHYAM | 37 | MALE | CRICKET | 1300 | 2004- 04-15 |
| 1245 | MEENA | 23 | FEMALE | VOLLEYBALL | 1000 | 2007- 06-18 |
| 1622 | AMRIT | 28 | MALE | KARATE | 1000 | 2007- 09-05 |
| 1256 | AMINA | 36 | FEMALE | CHESS | 1100 | 2003- 08-15 |
| 1720 | MANJU | 33 | FEMALE | KARATE | 1250 | 2004- 04-10 |
| 2321 | VIRAT | 35 | MALE | CRICKET | 1050 | 2005- 04-30 |
- SELECT COUNT(DISTINCT SPORTS) FROM CLUB;
- SELECT CNAME, SPORTS FROM CLUB
WHERE DOAPP<"2006-04-30" AND CNAME LIKE "%NA"; - SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER = "MALE" AND PAY BETWEEN 1000 AND 1200;
