English
CBSECommerce (English Medium) Class 12
Question Papers2489
Textbook Solutions20216
MCQ Online Mock Tests42
Important Solutions18873
Concept Notes & Videos242
Time Tables23
Syllabus

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 - Computer Science (Python)

Advertisements
Advertisements

Question

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
  1. SELECT MIN(PRICE), MAX(PRICE) FROM COMPUTER;
  2. SELECT COMPANY, COUNT(*) FROM COMPUTER GROUP BY COMPANY HAVING COUNT(COMPANY) > 1;
  3. SELECT PROD_NAME, QTY_SOLD FROM COMPUTER C, SALES S WHERE C.PROD_ID=S.PROD_ID AND TYPE = 'INPUT';
  4. SELECT PROD_NAME, COMPANY, QUARTER FROM COMPUTER C, SALES S WHERE C.PROD_ID=S.PROD_ID;
Code Writing

Solution


  1. Min(Price) Max(Price)
    200 4300

  2. Company Count(*)
    LOGITECH 2
    CANON 2

  3. PROD_NAME QTY_SOLD
    MOUSE 3
    KEYBOARD 2
    JOYSTICK 2

  4. PROD_NAME COMPANY QUARTER
    MOUSE LOGITECH 2
    LASER PRINTER CANON 1
    KEYBOARD LOGITECH 2
    JOYSTICK IBALL 1
shaalaa.com
SQL for Data Query
  Is there an error in this question or solution?
Q 28.a
2022-2023 (March) Set 4

APPEARS IN

2022-2023 (March) Set 4 (with solutions)
Q 28.a | 2 marks

RELATED QUESTIONS

Which function is used with the ORDER BY clause to custom sort order?


Which of the following is not true for SQL?


Which of the following is not a constraint?


Which constraint ensures that a column cannot have NULL values where NULL means missing/unknown/not applicable value?


How will you retrieve all details of employee_detail table?


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.


______ operator defines the range of values in which the common value must fall into to make condition true.


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 -
  1. Display all the information from the Movie table.
  2. 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.
  3. List the different categories of movies.
  4. 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.
  5. List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
  6. List details of all movies which fall in the category of comedy or action.
  7. List details of all movies which have not been released yet.

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 details of all movies which fall in the category of comedy or action.


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 have not been released yet.


Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

  1. Display the MatchID of all those matches where both teams have scored more than 70.
  2. Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
  3. Display the MatchID and date of matches played by Team 1 and won by it.
  4. Display the MatchID of matches played by Team 2 and not won by it.
  5. 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.


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 output of the queries (i) to (iv) based on the table, WORKER given below:

TABLE: WORKER

W_ID F_NAME L_NAME CITY STATE
102 SAHIL KHAN KANPUR UTTAR PRADESH
104 SAMEER PARIKH ROOP NAGAR PUNJAB
105 MARY JONES DELHI DELHI
106 MAHIR SHARMA SONIPAT HARYANA
107 ATHARVA BHARDWAJ DELHI DELHI
108 VEDA SHARMA KANPUR UTTAR PRADESH
  1. SELECT F_NAME, CITY FROM WORKER ORDER BY STATE DESC;
  2. SELECT DISTINCT (CITY) FROM WORKER;
  3. SELECT F_NAME, STATE FROM WORKER WHERE L_NAME LIKE '_HA%';
  4. SELECT CITY, COUNT(*) FROM WORKER GROUP BY CITY;

Consider the tables PRODUCT and BRAND given below:

Table: PRODUCT
PCode PName UPrice Rating BID
P01 Shampoo 120 6 M03
P02 Toothpaste 54 8 M02
P03 Soap 25 7 M03
P04 Toothpaste 65 4 M04
P05 Soap 38 5 M05
P06 Shampoo 245 6 M05
Table: BRAND
BID BName
M02 Dant Kanti
M03 Medimix
M04 Pepsodent
M05 Dove

Write SQL queries for the following:

  1. Display product name and brand name from the tables PRODUCT and BRAND.
  2. Display the structure of the table PRODUCT.
  3. Display the average rating of Medimix and Dove brands.
  4. Display the name, price, and rating of products in descending order of rating.

Share
Notifications

Englishहिंदीमराठी
Login
Create free account


      Forgot password?
Course
Use app×