Structured Query Language NCERT Class 12 CS Solution

Structured Query Language NCERT Solution || Structured Query Language Class 12 Solution || Structured Query Language Python Class 12 CS Solution || Structured Query Language Computer Science Solution || NCERT Structured Query Language Solution || Structured Query Language in Python Class 12 CS || Structured Query Language in Python Solution

Q1. Answer the following questions:

(a) Define RDBMS. Name any two RDBMS software.

(b) What  is  the  purpose  of  the  following  clauses  in  a  select statement?
i) ORDER BY
ii) GROUP BY

(c) Site any two differences between Single Row Functions and Aggregate Functions.

(d) What do you understand by Cartesian Product?

(e) Differentiate between the following statements:
i)  ALTER and UPDATE
ii) DELETE and DROP

(f) Write  the  name  of  the  functions  to  perform  the  following operations:
i) To display the day like “Monday”, “Tuesday”, from the date when India got independence.
ii) To display the specified number of characters from a particular position of the given string.
iii) To  display  the  name  of  the  month  in  which  you were born.
iv) To display your name in capital letters.

Q2. Write the output produced by the following SQL commands:

a) SELECT POW(2,3);
b) SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);
c) SELECT LENGTH("Informatics Practices");
d) SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4);
f) SELECT MID("Informatics",3,4), SUBSTR("Practices",3);





Q3. Consider the following MOVIE database and answer the SQL queries based on it.

a) Retrieve movies information without mentioning their column names.
b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
c) List the different categories of movies.
d) Find the net profit of each movie showing its ID, Name and Net Profit.
(Hint: Net Profit = BusinessCost – ProductionCost)
Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation.  If no, then what name is coined for such columns?  What can you say about the profit of a movie which has not yet released? Does your query result show profit as zero?
e) List  all  movies  with  ProductionCost  greater  than  80,000  and  less  than  1,25,000  showing  ID,  Name and ProductionCost.
f) List all movies which fall in the category of Comedy or Action.
g) List the movies which have not been released yet.




Q4. Suppose your school management has decided to conduct cricket matches between students of class XI and Class XII.  Students  of  each  class  are  asked  to  join  any  one  of  the  four  teams  —  Team  Titan,  Team  Rockers,  Team  Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

a) Create a database “Sports”.
b) Create a table “TEAM” with following considerations:

i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
ii) Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

c) Using table level constraint, make TeamID as primary key.
d) Show the structure of the table TEAM using SQL command.
e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Hurricane)

f) Show the contents of the table TEAM.
g) Now create another table below. MATCH_DETAILS and insert data as shown in table. Choose appropriate domains and constraints for each attribute.

Table: MATCH_DETAILS

h) Use the foreign key constraint in the MATCH_DETAILS table with reference to TEAM table so that MATCH_DETAILS table records score of teams existing in the TEAM table only.




Q5. Using the sports database containing two relations (TEAM, MATCH_DETAILS), answer the following relational algebra queries.
a) Retrieve the MatchID of all those matches where both the teams have scored > 70.
b) Retrieve  the  MatchID  of  all  those  matches  where  FirstTeam  has  scored  <  70  but SecondTeam  has scored > 70.
c) Find out the MatchID and date of matches played by Team 1 and won by it.
d) Find out the MatchID of matches played by Team 2 and not won by it.
e) In the TEAM relation, change the name of the relation to T_DATA.  Also change the attributes TeamID and TeamName to T_ID and T_NAME respectively.




Q6. A shop called Wonderful Garments that sells school uniforms maintain a database SCHOOL_UNIFORM as shown below.  It consisted of two relations — UNIFORM and PRICE. They made UniformCode as the primary key for UNIFORM relation. Further, they used UniformCode and Size as composite keys for PRICE relation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies.

Table: UNIFORM

Table: PRICE

a) The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL query to change the name of the relation PRICE to COST.
b) M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of `100 each. Insert this record in COST table.
c) When you used the above query to insert data, you were able to enter the values for handkerchief without entering its details in the UNIFORM relation. Make a provision so that the data can be entered in COST table only if it is already there in UNIFROM table.
d) Further, you should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraint to the SCHOOL_UNIFORM database.
e) ALTER table to add the constraint that price of an item is always greater than zero.



Q7.Consider the following table named “Product”, showing details of products being sold in a grocery  shop.

PCode    PName    UPrice    Manufacturer
P01    Washing Powder    120    Surf
P02    Tooth Paste    54    Colgate
P03    Soap    25    Lux
P04    Tooth Paste    65    Pepsodant
P05    Soap    38    Dove
P06    Shampoo    245    Dove


a)Write SQL queries for the following:

i. Create the table Product with appropriate data types and constraints.
ii. Identify the primary key in Product.
iii. List the Product Code, Product name and price in descending order of their product name. If PName is the same then display the data in ascending order of price.
iv. Add a new column Discount to the table Product.
v. Calculate the value of the discount in the table Product as 10 percent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0.
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
vii. Display   the   total   number   of   products   manufactured by each manufacturer.

b) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:

i.SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;
ii.SELECT   DISTINCT   Manufacturer   FROM   Product;Chapter 1.indd   2411/26/2020   12:31:31 PM




Q8. Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

a) Add a new column Discount in the INVENTORY table.
b) Set   appropriate discount values for all cars keeping in mind the following:

(i) No discount is available on the LXI model.
(ii) VXI model gives a 10% discount.
(iii)A 12% discount is given on cars other than   LXI model and VXI model.

c) Display the name of the costliest car with fuel type “Petrol”.
d) Calculate the average discount and total discount available on Car4.
e) List the total number of cars having no discount.