Type A: Short Answer Questions/Conceptual Questions

Question 1

Define a function.

Answer

A function is a special type of predefined command set that performs some operation and
returns a single value.

Question 2(i)

Explain the UCASE() SQL function using suitable examples.

Answer

The UCASE()/UPPER() function converts the given string into uppercase. The syntax is
UPPER(str) or UCASE(str). It returns the argument str with all letters capitalized, and the return
value has the same data type as the argument str. For example,

SELECT UPPER('Large') "Uppercase";

Output

+-----------+

| Uppercase |

+-----------+

| LARGE |

+-----------+

Question 2(ii)

Explain the TRIM() SQL function using suitable examples.

Answer

The TRIM() function removes leading and trailing spaces from a given string. It performs the
combined functions of LTRIM() and RTRIM(). The syntax is:

TRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str) or TRM([remstr FROM] str).

It returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers
(BOTH, LEADING, or TRAILING) are given, BOTH is assumed. remstr is optional, and if not
specified, spaces are removed. For example,

SELECT TRIM(' Bar One ');

Output

+----------------------+

| TRIM(' Bar One ') |

+----------------------+

| Bar One |

+----------------------+

Question 2(iii)

Explain the MID() SQL function using suitable examples.

Answer

The MID() function returns a substring starting from the specified position. The syntax is:

MID(str, pos, len).

It returns a substring from str starting at position pos and containing len characters. For
example,

SELECT MID('Quadratically', 5, 6);

Output

+---------------------------+

| MID('Quadratically', 5,6) |

+---------------------------+

| ratica |

+---------------------------+

Question 2(iv)

Explain the DAYNAME() SQL function using suitable examples.

Answer

The DAYNAME() function returns the name of weekday for date. The syntax is DAYNAME(date).
For example,

SELECT DAYNAME('2024-05-20');

Output

+-----------------------+

| DAYNAME('2024-05-20') |

+-----------------------+

| Monday |

+-----------------------+
Question 2(v)

Explain the POWER() SQL function using suitable examples.

Answer

The POWER()/POW() function returns the value of m raised to the nth power, denoted as mn.
The syntax is POWER(m, n) or POW(m, n). Both m and n can be any numbers, but if m is
negative, n must be an integer. For example,

SELECT POWER(4, 2) "Raised";

Output

+--------+

| Raised |

+--------+

| 16 |

+--------+

Question 3(i)

What will be the output of following code ?

mysql> SELECT CONCAT(CONCAT('Inform', 'atics'), 'Practices');

Answer

Output

+------------------------------------------------+

| CONCAT(CONCAT('Inform', 'atics'), 'Practices') |

+------------------------------------------------+
| InformaticsPractices |

+------------------------------------------------+

Explanation

The CONCAT() function in SQL is used to concatenate two or more strings into a single string.
In this query, the inner CONCAT('Inform', 'atics') concatenates 'Inform' and 'atics' to produce
'Informatics'. The outer CONCAT() then concatenates 'Informatics' with 'Practices' to produce
'InformaticsPractices'. Therefore, the final output is 'InformaticsPractices'.

Question 3(ii)

What will be the output of following code ?

mysql> SELECT LCASE('INFORMATICS PRACTICES CLASS 11TH');

Answer

Output

+-------------------------------------------+

| LCASE('INFORMATICS PRACTICES CLASS 11TH') |

+-------------------------------------------+

| informatics practices class 11th |

+-------------------------------------------+

Explanation

The LCASE() function in SQL is used to convert all characters of a given string to lowercase. In
the query, LCASE('INFORMATICS PRACTICES CLASS 11TH') converts the entire string to
'informatics practices class 11th'. Therefore, the output is 'informatics practices class 11th'.

Question 3(iii)

What will be the output of following code ?

mysql> SELECT UCASE('Computer studies');

Answer

Output

+---------------------------+

| UCASE('Computer studies') |

+---------------------------+

| COMPUTER STUDIES |

+---------------------------+

Explanation

The UCASE() function in SQL is used to convert all characters of a given string to uppercase. In
this query, UCASE('Computer studies') converts the entire string to 'COMPUTER STUDIES'.
Therefore, the output is 'COMPUTER STUDIES'.

Question 3(iv)

What will be the output of following code ?

mysql> SELECT CONCAT(LOWER('Class'), UPPER('xii'));

Answer

Output

+--------------------------------------+

| CONCAT(LOWER('Class'), UPPER('xii')) |

+--------------------------------------+

| classXII |
+--------------------------------------+

Explanation

In the query, LOWER('Class') converts 'Class' to lowercase, resulting in 'class', and UPPER('xii')
converts 'xii' to uppercase, resulting in 'XII'. The CONCAT() function then combines these two
results, producing the final output 'classXII'.

Question 4

If Str = "INFORMATICS PRACTICES ...." and Str1 = "... FOR CLASS XI"

Write commands to print the output as 'informatics practices for class xi'

Answer

SELECT CONCAT(LCASE('INFORMATICS PRACTICES'), ' ', LCASE('FOR CLASS XI'));

Output

+--------------------------------------------------------------------+

| CONCAT(LCASE('INFORMATICS PRACTICES'), ' ', LCASE('FOR CLASS XI')) |

+--------------------------------------------------------------------+

| informatics practices for class xi |

+--------------------------------------------------------------------+

Question 5

Write commands to display the system date.

Answer

SELECT SYSDATE();
Output

+---------------------+

| SYSDATE() |

+---------------------+

| 2024-05-20 15:31:25 |

+---------------------+

Question 6

Write a command to display the name of current month.

Answer

SELECT MONTHNAME(CURDATE());

Output

+----------------------+

| MONTHNAME(CURDATE()) |

+----------------------+

| May |

+----------------------+

Question 7

Write SQL statement to display

Today, the date is <current date>

Answer

SELECT CONCAT('Today, the date is ', CURDATE()) AS CURDATE;

Output

+-------------------------------+

| CURDATE |

+-------------------------------+

| Today, the date is 2024-05-20 |

+-------------------------------+

Question 8

Write command to print the day of the week of your Birthday in the year 1999.

Answer

SELECT DAYOFWEEK('1999-07-21');

Output

+-------------------------+

| DAYOFWEEK('1999-07-21') |

+-------------------------+

| 4|

+-------------------------+

Question 9

Write a command to display the current time.

Answer

SELECT TIME(NOW());

Output
+-------------+

| TIME(NOW()) |

+-------------+

| 15:40:57 |

+-------------+

Question 10

Consider two fields B_date, which stores the birth date and J_date, which stores the joining date
of an employee. Write commands to find out and display the approximate age of an employee
as on today.

Answer

SELECT (YEAR(CURDATE()) - YEAR(B_DATE)) AS AGE FROM EMPLOYEE;

Question 11(i)

Find the output of the following SQL Query :

SELECT ROUND(7658.345, 2);

Answer

Output

+--------------------+

| ROUND(7658.345, 2) |

+--------------------+

| 7658.35 |

+--------------------+

Explanation
In the query, the ROUND function is used to round a number to 2 decimal places, resulting in
7658.35.

Question 11(ii)

Find the output of the following SQL Query :

SELECT MOD(ROUND (13.9, 0), 3);

Answer

Output

+------------------------+

| MOD(ROUND(13.9, 0), 3) |

+------------------------+

| 2|

+------------------------+

Explanation

In the above query, 13.9 is first rounded to the nearest whole number (0 decimal places),
resulting in 14 using the ROUND function. Then, the MOD function computes the remainder of
14 divided by 3, resulting in 2. Therefore, the output of the query is 2.

Question 12

Write the SQL functions which will perform the following operations :

(i) To display the name of the month of the current date.

(ii) To remove spaces from the beginning and end of a string, "Panorama".
(iii) To display the name of the day e.g., Friday or Sunday from your date of birth, dob.

(iv) To display the starting position of your first name(fname) from your whole name (name).

(v) To compute the remainder of division between two numbers, n1 and n2.

Answer

(i)

SELECT MONTHNAME(CURDATE());

Output

+----------------------+

| MONTHNAME(CURDATE()) |

+----------------------+

| May |

+----------------------+

(ii)

SELECT TRIM(" Panorama ");

Output

+----------------------+

| TRIM(" Panorama ") |

+----------------------+

| Panorama |
+----------------------+

(iii)

SELECT DAYNAME('2000-07-22');

Output

+-----------------------+

| DAYNAME('2000-07-22') |

+-----------------------+

| Saturday |

+-----------------------+

(iv)

SELECT INSTR('Gupta Ashwini', 'Ashwini') AS StartingPosition;

Output

+------------------+

| StartingPosition |

+------------------+

| 7|

+------------------+

(v)

SELECT MOD(n1, n2);

Question 13

Write suitable SQL query for the following :

(i) Display 7 characters extracted from 7th left character onwards from the string 'INDIA
SHINING'.

(ii) Display the position of occurrence of string 'COME' in the string 'WELCOME WORLD'.

(iii) Round off the value 23.78 to one decimal place.

(iv) Display the remainder of 100 divided by 9.

(v) Remove all the expected leading and trailing spaces from a column userid of the table
'USERS'.

Answer

(i)

SELECT SUBSTR('INDIA SHINING', 7, 7);

Output

+-------------------------------+

| SUBSTR('INDIA SHINING', 7, 7) |

+-------------------------------+

| SHINING |

+-------------------------------+

(ii)

SELECT INSTR('WELCOME WORLD', 'COME');

Output

+--------------------------------+

| INSTR('WELCOME WORLD', 'COME') |

+--------------------------------+

| 4|

+--------------------------------+

(iii)

SELECT ROUND(23.78, 1);

Output

+-----------------+

| ROUND(23.78, 1) |

+-----------------+

| 23.8 |

+-----------------+

(iv)

SELECT MOD(100, 9);

Output

+-------------+

| MOD(100, 9) |

+-------------+

| 1|

+-------------+

(v)
SELECT TRIM(userid) FROM USERS;

Type B: Short Answer Questions

Question 1

Based on the SQL table CAR_SALES, write suitable queries for the following :

NUMBER SEGMENT FUEL QT1 QT2

1 Compact HatchBack Petrol 56000 70000

2 Compact HatchBack Diesel 34000 40000

3 MUV Petrol 33000 35000

4 MUV Diesel 14000 15000

5 SUV Petrol 27000 54000

6 SUV Diesel 18000 30000

7 Sedan Petrol 8000 10000

8 Sedan Diesel 1000 5000

(i) Display fuel wise average sales in the first quarter.

(ii) Display segment wise highest sales in the second quarter.

(iii) Display the records in the descending order of sales in the second quarter.

Answer

(i)
SELECT FUEL, AVG(QT1) AS Avg_Sales_QT1

FROM CAR_SALES

GROUP BY FUEL;

Output

+--------+---------------+

| FUEL | Avg_Sales_QT1 |

+--------+---------------+

| Petrol | 31000.0000 |

| Diesel | 16750.0000 |

+--------+---------------+

(ii)

SELECT SEGMENT, MAX(QT2) AS HIGHEST_SALES

FROM CAR_SALES

GROUP BY SEGMENT;

Output

+-------------------+---------------+

| SEGMENT | HIGHEST_SALES |

+-------------------+---------------+

| Compact HatchBack | 70000 |

| MUV | 35000 |

| SUV | 54000 |

| Sedan | 10000 |

+-------------------+---------------+

(iii)
SELECT * FROM CAR_SALES

ORDER BY QT2 DESC;

Output

+--------+-------------------+--------+-------+-------+

| NUMBER | SEGMENT | FUEL | QT1 | QT2 |

+--------+-------------------+--------+-------+-------+

| 1 | Compact HatchBack | Petrol | 56000 | 70000 |

| 5 | SUV | Petrol | 27000 | 54000 |

| 2 | Compact HatchBack | Diesel | 34000 | 40000 |

| 3 | MUV | Petrol | 33000 | 35000 |

| 6 | SUV | Diesel | 18000 | 30000 |

| 4 | MUV | Diesel | 14000 | 15000 |

| 7 | Sedan | Petrol | 8000 | 10000 |

| 8 | Sedan | Diesel | 1000 | 5000 |

+--------+-------------------+--------+-------+-------+

Question 2

Predict the output of the following queries based on the table CAR_SALES given below :

NUMBER SEGMENT FUEL QT1 QT2

1 Compact HatchBack Petrol 56000 70000

2 Compact HatchBack Diesel 34000 40000

3 MUV Petrol 33000 35000

4 MUV Diesel 14000 15000

5 SUV Petrol 27000 54000

6 SUV Diesel 18000 30000

7 Sedan Petrol 8000 10000

8 Sedan Diesel 1000 5000

(i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";

(ii) SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";

(iii) SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";

Answer

(i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";

Output

+------------------+

| LEFT(SEGMENT, 2) |

+------------------+

| Co |

| MU |

| SU |

| Se |

+------------------+

Explanation

In the query SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";, the
function LEFT(SEGMENT, 2) takes the leftmost characters of each SEGMENT value, starting
from the first character, and returns two characters. The WHERE FUEL = 'PETROL' clause filters
the rows to include only those with 'PETROL' as the fuel type.
(ii) SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";

Output

+------------+

| AVG SALE |

+------------+

| 13500.0000 |

| 6000.0000 |

+------------+

Explanation

The SQL query SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";
calculates the average sale for the "SUV" segment in the CAR_SALES table. It does this by
subtracting the first quarter sales (QT1) from the second quarter sales (QT2) for each record in
the "SUV" segment and then dividing the result by 2. The alias "AVG SALE" is assigned to the
computed value.

(iii) SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";

Output

+----------+

| TOT SALE |

+----------+

| 67000 |

+----------+

Explanation

The query SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";
calculates the total sales for the "DIESEL" fuel type in the CAR_SALES table. It does this by
summing up the values in the QT1 column for rows where the FUEL column is equal to "DIESEL".
The alias "TOT SALE" is assigned to the computed sum.

Question 3

Given the following table :

Table : STUDENT1

No. Name Stipend StreamAvgMark Grade Class

1 Karan 400.00 Medical 78.5 B 12B

2 Divakar 450.00 Commerce 89.2 A 11C

3 Divya 300.00 Commerce 68.6 C 12C

4 Arun 350.00 Humanities 73.1 B 12C

5 Sabina 500.00 Nonmedical 90.6 A 11A

6 John 400.00 Medical 75.4 B 12B

7 Robert 250.00 Humanities 64.4 C 11A

8 Rubina 450.00 Nonmedical 88.5 A 12A

9 Vikas 500.00 Nonmedical 92.0 A 12A

10 Mohan 300.00 Commerce 67.5 C 12C

Give the output of following SQL statement :

(i) SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;

(ii) SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;

(iii) SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;

(iv) SELECT RIGHT(Stream, 2) FROM Student1 ;

Answer

(i) It will return error because no argument is passed as decimal places to truncate. Syntax of
truncate function is TRUNCATE(number, decimals).

(ii)

Output

+----------------+

| ROUND(AvgMark) |

+----------------+

| 78 |

| 73 |

| 75 |

+----------------+

(iii)

Output

+----------------------+

| CONCAT(Name, Stream) |

+----------------------+

| RubinaNonmedical |
| VikasNonmedical |

+----------------------+

(iv)

Output

+------------------+

| RIGHT(Stream, 2) |

+------------------+

| al |

| ce |

| ce |

| es |

| al |

| al |

| es |

| al |

| al |

| ce |

+------------------+

Question 4

Given the table LIBRARY :

No Title Author Type Pub Qty Price

1 Data Structure Lipschutz DS McGraw 4 217

2 Computer Studies French FND Galgotia 2 75

3 Advanced Pascal Schildt PROG McGraw 4 350

4 Dbase dummies Palmer DBMS PustakM 5 130

5 Mastering C + + Gurewich PROG BPB 3 295

6 Guide Network Freed NET ZPress 3 200

7 Mastering Foxpro Seigal DBMS BPB 2 135

8 DOS guide Norton OS PHI 3 175

9 Basic for Beginners MortonPROG BPB 3 40

10 Mastering Window Cowart OS BPB 1 225

Give the output of following SQL commands on the basis of table Library.

(i) SELECT UPPER(Title) FROM Library WHERE Price < 150 ;

(ii) SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3 ;

(iii) SELECT MOD(Qty, 4) FROM Library ;

Answer

(i) SELECT UPPER(Title) FROM Library WHERE Price < 150 ;

Output

+---------------------+

| UPPER(Title) |

+---------------------+

| COMPUTER STUDIES |
| DBASE DUMMIES |

| MASTERING FOXPRO |

| BASIC FOR BEGINNERS |

+---------------------+

Explanation

The SQL query SELECT UPPER(Title) FROM Library WHERE Price < 150; returns the uppercase
version of the Title column for all rows in the LIBRARY table where the Price column is less than
150.

(ii) SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3 ;

Output

+----------------------+

| CONCAT(Author, Type) |

+----------------------+

| FrenchFND |

| SeigalDBMS |

| CowartOS |

+----------------------+

Explanation

The query SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3; concatenates the
Author and Type columns using the CONCAT() function from the LIBRARY table for books that
have a quantity less than 3.

(iii) SELECT MOD(Qty, 4) FROM Library ;

Output
+-------------+

| MOD(Qty, 4) |

+-------------+

| 0|

| 2|

| 0|

| 1|

| 3|

| 3|

| 2|

| 3|

| 3|

| 1|

+-------------+

Explanation

The SQL query SELECT MOD(Qty, 4) FROM Library; calculates the remainder when each book's
quantity (Qty) in the LIBRARY table is divided by 4 using the MOD() function.

Question 5

Write a query to show the current date and time.

Answer

SELECT NOW();

Output

+---------------------+
| NOW() |

+---------------------+

| 2024-05-21 12:20:03 |

+---------------------+

Question 6

Perform the following question based on these tables :

table PAYDAY (contains one column only)

CycleDate DATE

table ADDRESS ( contains following eight columns)

LastName VARCHAR(25),

FirstName VARCHAR(25),

Street VARCHAR(50),

City VARCHAR(25)

State CHAR(2),

Zip NUMBER,

Phone VARCHAR(12),

Ext VARCHAR(5)

Write a query to show the city of user with first name as 'MARK'.

Answer
SELECT City

FROM Users

WHERE FirstName = 'MARK';

Question 7

Show via query how many days remain until Christmas. Round fractional days up using the
numeric function ROUND.

Answer

SELECT ROUND(DAYOFYEAR('2024-12-25') - DAYOFYEAR(CURDATE())) AS DaysUntilChristmas;

Output

+--------------------+

| DaysUntilChristmas |

+--------------------+

| 218 |

+--------------------+

Question 8

Write the SQL queries which will perform the following operations :

(i) To display the year from your Date of Admission which is '2023-05-15'.

(ii) To convert your email id [email protected] to lowercase.

(iii) To remove leading spaces from a string 'my country'.

(iv) To display current date.

(v) To display the value of 106.

Answer

(i)

SELECT YEAR('2023-05-15');

Output

+--------------------+

| YEAR('2023-05-15') |

+--------------------+

| 2023 |

+--------------------+

(ii)

SELECT LCASE('[email protected]');

Output

+----------------------+

| LCASE('[email protected]') |

+----------------------+

| [email protected] |

+----------------------+

(iii)
SELECT LTRIM(' my country');

Output

+---------------------+

| LTRIM('my country') |

+---------------------+

| my country |

+---------------------+

(iv)

SELECT CURDATE();

Output

+------------+

| CURDATE() |

+------------+

| 2024-05-21 |

+------------+

(v)

SELECT POWER(10, 6);

Output

+--------------+

| POWER(10, 6) |

+--------------+

| 1000000 |

+--------------+
Question 9

Write a query against the EMPL table to show the names of all employees concatenated with
their jobtypes.

Answer

SELECT CONCAT(NAME, JOBTYPE)

FROM EMPL;

Question 10

Write a query against the ADDRESS table to show the names (first name, last name) and phones
of all persons concatenated in following form :

TinaSeth23456789

MoradK.22211890

Table ADDRESS ( contains following eight columns)

LastName VARCHAR(25),

FirstName VARCHAR(25),

Street VARCHAR(50),

City VARCHAR(25)

State CHAR(2),

Zip NUMBER,

Phone VARCHAR(12),

Ext VARCHAR(5)

Answer
SELECT CONCAT(FIRSTNAME, LASTNAME, PHONE)

FROM ADDRESS;

Question 11

Write a query against the ADDRESS table to select a list of names and phone numbers. The
output should match these requirements :

The name column should contain both the first and last names with a blank space between
them. Use the string concatenation.

The second column will contain the phone number.

Phone number should be in the format (999) 999-9999. Use the SUBSTR function and CONCAT.

Order the query by last name then first name.

Table ADDRESS ( contains following eight columns)

LastName VARCHAR(25),

FirstName VARCHAR(25),

Street VARCHAR(50),

City VARCHAR(25)

State CHAR(2),

Zip NUMBER,

Phone VARCHAR(12),

Ext VARCHAR(5)

Answer

SELECT CONCAT(FirstName, ' ', LastName) AS Name,

CONCAT('(', SUBSTR(Phone, 1, 3), ') ',

SUBSTR(Phone, 4, 3), '-',

SUBSTR(Phone, 7, 4)) AS Phone_Number

FROM ADDRESS

ORDER BY LastName, FirstName;