CBSE Class 12 Informatics Practices Querying and SQL Functions Case Study Questions Set 01

Answer: (d) Both Query 2 and Query 3 will display the desired data.

Explanation:
1. The rows must be sorted on the StuID column, so Query 1 (sorted on BookID) is ruled out.
2. ORDER BY sorts in ascending order by default, so Order By StuID and Order By StuID ASC give the same result.
3. DESC sorts in descending order, so Query 4 is wrong.
4. So both Query 2 and Query 3 display the desired data.

Teacher's Note:
1. ASC is the default sort order, so writing it is optional.
2. Always check which column a query sorts on, not just the ASC or DESC keyword.

Answer: (c) BookID, Issued_date

Explanation:
1. A primary key must be unique for every row of the table.
2. The same book is issued many times over the year, so BookID alone repeats in StuLibrary. Issued_date alone also repeats because many books are issued on one day.
3. BookID with StuID also fails, because the same student may borrow the same book again later.
4. A book is issued for at least one day, so one book cannot be issued twice on the same date. Hence the pair BookID, Issued_date identifies each row uniquely.

Teacher's Note:
1. A primary key made of two or more columns is called a composite primary key.
2. The line about the minimum issue duration of one day is the clue that makes BookID with Issued_date unique.

Answer: (d) SELECT BookID from StuLibrary where Return_date is NULL;

Explanation:
1. When a book is issued, Return_date is left empty (NULL) until the book comes back.
2. So the books not yet returned are the rows where Return_date has no value.
3. The query is SELECT BookID from StuLibrary where Return_date is NULL;

Teacher's Note:
1. Use IS NULL to test for a missing value; = NULL never matches any row.
2. BookID, StuID and Issued_date are always filled in when a book is issued.

Answer: (c) StuAadhar

Explanation:
1. StuID is the primary key of the Student table.
2. An alternate key is another candidate key, that is, a column that is also unique for every student but is not chosen as the primary key.
3. Names and classes repeat, and a contact number may be shared by brothers and sisters. Every person has a different Aadhaar number.
4. So the alternate key is StuAadhar.

Teacher's Note:
1. Candidate keys = primary key + alternate keys.
2. A column is a key only if it can never repeat, not just if it happens to be unique in the current data.

Answer: (c) SELECT Issued_date from StuLibrary GROUP BY Issued_date having count(*)>5;

Explanation:
1. To count books issued on each date, the rows are grouped on Issued_date.
2. A condition on a group (here count(*)>5) is written with HAVING, not WHERE. WHERE cannot come after GROUP BY, so options (a) and (d) give a syntax error.
3. Option (b) groups on Return_date, which counts books returned on each date, not issued.
4. So the query is SELECT Issued_date from StuLibrary GROUP BY Issued_date having count(*)>5;

Teacher's Note:
1. WHERE filters rows before grouping; HAVING filters the groups after GROUP BY.
2. Aggregate functions such as COUNT(*) cannot be used in a WHERE clause.

Answer: (d) Select carname, round(cost,1) from carmarket;

Explanation:
1. ROUND(number, d) rounds the number to d digits after the decimal point.
2. round(cost) has no second argument, so it rounds to 0 decimal places (5.90 becomes 6).
3. round.cost(1) is not valid SQL syntax.
4. So the query is Select carname, round(cost,1) from carmarket; (for example 62.38 becomes 62.4).

Teacher's Note:
1. The column name goes inside the brackets of the function: ROUND(cost, 1).
2. A negative second argument rounds to the left of the decimal point, e.g. ROUND(62.38,-1) gives 60.

Answer: (b) select carname,color,instr(color,'E') from carmarket;

Explanation:
1. INSTR(string, substring) returns the position of the first occurrence of the substring, or 0 if it is not found.
2. So instr(color,'E') gives 4 for BLUE, 5 for SILVER, 2 for RED and 0 for BLACK.
3. Options (a) and (c) only filter rows and do not show any position; SUBSTR in (d) extracts characters, it does not find a position.
4. So the query is select carname,color,instr(color,'E') from carmarket;

Teacher's Note:
1. INSTR counts positions from 1, not from 0.
2. The wildcards % and _ work only with LIKE; with = they are ordinary characters.

Answer: (a) select carname,lcase(company) from carmarket where year(dom) = 2020;

Explanation:
1. LCASE() (same as LOWER()) converts text to lower case.
2. YEAR(dom) extracts the year from the date. There are no functions called yearof() or yearfrom(), and FROM ... TO is not valid in WHERE.
3. So the query is select carname,lcase(company) from carmarket where year(dom) = 2020; which shows INDIGO tata and GLC mercedes.

Teacher's Note:
1. The date functions YEAR(), MONTH() and DAY() return numbers, so compare them with numbers like 2020.
2. A date range is written with BETWEEN '2020-01-01' AND '2020-12-31'.

Answer: (b) select count(*),year(dom) from carmarket group by year(dom);

Explanation:
1. "Each year" means the rows must be grouped on the year, using GROUP BY year(dom).
2. count(*) then counts the rows in each group.
3. count(carmarket) uses the table name as a column, and count(distinct *) is not allowed, so (c) and (d) give errors.
4. So the query is select count(*),year(dom) from carmarket group by year(dom); (2 cars in 2016, 1 each in 2017, 2018 and 2019, 2 in 2020).

Teacher's Note:
1. You can group on an expression such as YEAR(dom), not only on a column.
2. COUNT(*) counts rows; COUNT(column) counts only the non-NULL values of that column.

Answer: (c) Cardinality = 7 and Degree = 6

Explanation:
1. Cardinality is the number of rows (tuples): the table has 7 cars, C01 to C07.
2. Degree is the number of columns (attributes): CARCODE, CARNAME, COMPANY, COLOR, COST and DOM make 6.
3. So Cardinality = 7 and Degree = 6.

Teacher's Note:
1. Remember: cardinality counts rows and degree counts columns.
2. The heading row is not counted in the cardinality.

Answer: (a) Select left(COLOUR,3) from GARMENT where COLOUR like "B%";

Explanation:
1. The output shows the first 3 letters of the colours that start with B (Blue, Black, Blue, Brown).
2. COLOUR like "B%" picks those four rows, and left(COLOUR,3) takes 3 characters from the left.
3. mid(COLOUR,3) would start at the 3rd character (ue, ack, ue, own), and (b) gives Red and Pink.
4. So the command is Select left(COLOUR,3) from GARMENT where COLOUR like "B%";

Teacher's Note:
1. LEFT(str,n) takes n characters from the start; MID(str,p,n) starts at position p.
2. With = the % sign is just a character, so option (d) returns no rows.

Answer: (c) Delete from GARMENT where GCode =116;

Explanation:
1. The syntax is DELETE FROM table WHERE condition; with no * after DELETE, so (a) is wrong.
2. The table is named GARMENT; GARMENTS in (b) does not exist and gives an error.
3. IS is used only with NULL, so (d) is wrong.
4. So the command is Delete from GARMENT where GCode =116;

Teacher's Note:
1. DELETE removes whole rows, so no column list or * is written.
2. Without a WHERE clause, DELETE removes every row of the table.

Answer: (c)

Explanation:
1. order by Price Desc sorts the rows from the highest price to the lowest.
2. Prices in that order: 4000 (Jacket), 1600 (Jeans), 1500 (Trousers), 1400 (Tshirt), 1200 (LadiesTop), 1100 (Skirt).
3. So the output is option (c).

Teacher's Note:
1. Option (a) is the table order, (b) is ascending order and (d) is alphabetical order of names.
2. ORDER BY uses ascending order unless DESC is written.

Answer: (b) Update GARMENT set COLOUR ="Orange" where Gcode=116;

Explanation:
1. The syntax is UPDATE table SET column = value WHERE condition;
2. (a) uses the wrong table name GARMENTS, (c) uses IS instead of =, and (d) puts WHERE before SET and leaves Orange without quotes.
3. So the command is Update GARMENT set COLOUR ="Orange" where Gcode=116;

Teacher's Note:
1. SET always comes before WHERE in an UPDATE command.
2. Text values must be enclosed in quotes; MySQL accepts single or double quotes.

Answer: (a) Degree=5 & Cardinality=6

Explanation:
1. Degree is the number of columns: GCODE, GNAME, SIZE, COLOUR, PRICE = 5.
2. Cardinality is the number of rows: 6 garments (111 to 116).
3. So Degree = 5 & Cardinality = 6.

Teacher's Note:
1. Degree counts attributes (columns); cardinality counts tuples (rows).
2. Adding a new record changes the cardinality but not the degree.

Answer: (b) MySQL

Explanation:
1. Oracle, Sybase and Microsoft SQL Server are commercial (paid) database systems.
2. MySQL is an open-source RDBMS that can be downloaded and used free of cost.
3. So the answer is MySQL.

Teacher's Note:
1. MySQL is the RDBMS used in the CBSE Informatics Practices syllabus.
2. Free and open-source software (FOSS) can be used, studied and shared without paying a licence fee.

Answer: (d) All of the above

Explanation:
1. The output lists the salesmen who do not live in Delhi: Sushant (Gurgaon) and Priya (Noida).
2. Not Address ="Delhi", Address NOT IN("Delhi") and Address !="Delhi" all mean "Address is not Delhi".
3. All three give the same output, so the answer is All of the above.

Teacher's Note:
1. != and <> both mean "not equal to" in MySQL.
2. NOT IN is most useful with a list of values, e.g. NOT IN('Delhi','Noida').

Answer: (c) Select * from salesman where upper(area)='NORTH';

Explanation:
1. upper(area) converts every stored value (North, NORTH or north) to NORTH.
2. Comparing it with 'NORTH' therefore matches the area in any case. Here it returns Mohit and Priyanshi.
3. (a) and (d) are syntax errors, and in (b) the word north without quotes is read as a column name, which gives an error.
4. So the command is Select * from salesman where upper(area)='NORTH';

Teacher's Note:
1. Applying UPPER() (or LOWER()) to both sides is a safe way to compare text ignoring case.
2. Each condition joined by OR must be complete, e.g. area='North' OR area='NORTH'.

Answer: (b) Select sname, sales from Salesman where area="East" or area="West";

Explanation:
1. A row should be shown if its area is East or its area is West, so the two conditions are joined with OR.
2. With AND, (a) needs one area to be East and West at the same time, so it returns no rows.
3. IN needs a list in brackets, like IN("East","West"), so (c) and (d) are syntax errors.
4. So the command is Select sname, sales from Salesman where area="East" or area="West"; (Amit, Sushant and Priya).

Teacher's Note:
1. "East and West areas" in English becomes area='East' OR area='West' in SQL.
2. The same result is given by area IN ('East','West').

Answer: (a) Select sname, round(sales,1) from salesman;

Explanation:
1. In ROUND(x, d), d is the number of digits after the decimal point, so one decimal place is d = 1, not 0.1.
2. TRUNC() is not a MySQL function (MySQL has TRUNCATE()), and truncating cuts digits off instead of rounding.
3. So the command is Select sname, round(sales,1) from salesman; (for example 7000.75 becomes 7000.8).

Teacher's Note:
1. The second argument of ROUND() is a whole number of decimal places.
2. ROUND(7000.75,1) gives 7000.8, while TRUNCATE(7000.75,1) gives 7000.7.

Answer: (c)

Explanation:
1. Sname Like "P%" selects the names starting with P: Priya (3450.45) and Priyanshi (8000.62).
2. Right(Sname,3) takes the last 3 letters: Priya gives iya and Priyanshi gives shi.
3. Round(Sales) rounds to 0 decimal places: 3450.45 gives 3450 and 8000.62 gives 8001.
4. So the output is option (c).

Teacher's Note:
1. RIGHT() takes characters from the end of the string; LEFT() would give Pri for both names.
2. ROUND() looks at the first digit dropped: 8000.62 has 6 after the point, so it rounds up to 8001.

Answer: (c) Select Cust_Name from Customer where Cust_City like '%a';

Explanation:
1. Dhashmesh, Sanya and Ishaan live in Ambala, Patna and Agra: all city names that end with the letter a.
2. Cust_City like '%a' matches any city ending in a.
3. There is no column called Name, so (a) and (b) give an error; (d) looks for names starting with a and returns no rows.
4. So the query is Select Cust_Name from Customer where Cust_City like '%a';

Teacher's Note:
1. '%a' means ends with a, 'a%' means starts with a and '%a%' means contains a.
2. The column names in a query must match the table exactly.

Answer: (a)

Explanation:
1. ORDER BY without ASC or DESC sorts in ascending order.
2. Opening balances from lowest to highest: 10000 (Dhashmesh), 12000 (Mrinal), 13000 (Joe), 15000 (Sanya), 19000 (Ishaan).
3. So the output is option (a).

Teacher's Note:
1. Option (b) is descending order, (c) is alphabetical order of names and (d) is the original table order.
2. Column names are not case-sensitive in MySQL, so Open_bal and Open_Bal refer to the same column.

Answer: (c) Select Cust_City, Max(Open_Bal) from Customer group by Cust_City;

Explanation:
1. WHERE must be followed by a condition, so where group by is an error. The word WHERE must be removed.
2. "Each city" means grouping on Cust_City, and showing Cust_City in the output tells which maximum belongs to which city.
3. (a) has no FROM clause and (d) groups on customer name, which gives one row per customer.
4. So the command is Select Cust_City, Max(Open_Bal) from Customer group by Cust_City; (NewDelhi shows 13000).

Teacher's Note:
1. The order of clauses is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
2. Include the GROUP BY column in the SELECT list so that each result can be identified.

Answer: (c) Select count(*) from customer where open_bal between 15000 and 20000;

Explanation:
1. count(*) counts the rows that satisfy the WHERE condition.
2. The range is written as BETWEEN 15000 AND 20000; the word TO in (b) is a syntax error.
3. There is no column called total in (a), and (d) has no condition, so it counts all 5 rows.
4. So the command is Select count(*) from customer where open_bal between 15000 and 20000; which gives 2 (Sanya and Ishaan).

Teacher's Note:
1. BETWEEN includes both end values, so 15000 itself is counted.
2. A condition on individual rows goes in WHERE; HAVING is for groups.

Answer: (b) select left(cust_name,2) from customer;

Explanation:
1. LEFT(str, n) returns the first n characters of the string.
2. right(cust_name,2) gives the last two letters (sh, ya, ...), RIGHT() takes only two arguments, and mid(cust_name,2) gives the name from the 2nd letter onwards (hashmesh, anya, ...).
3. So the command is select left(cust_name,2) from customer; giving Dh, Sa, Jo, Mr, Is.

Teacher's Note:
1. LEFT(cust_name,2) gives the same result as MID(cust_name,1,2).
2. In MID() and SUBSTR() the second argument is the starting position, not the number of characters.