Chapter 5 : MySQL Revision Tour
Sumita Arora Book Exercise Solution of Class 12 Informatics Practices [065]
Check Point 5.1
1. What is SOL ? What are the different categories of SOL commands ?
Answer :- SQL Commands are classified into the following categories –
(a) Data Definition Language (DDL) commands
(b) Data Manipulation Language (DML) commands
(c) Transaction Control Language (TCL) commands
(d) Session Control Commands
(e) System Control Commands
2. What is a datatype ? Name some datatypes available in MySQL.
Answer :- Datatype tells about the type of data and associated operation upon it.
Some datatypes available in MySQL are – int, decimal, char, varchar, date, etc.
3. How would you calculate 13 * 15 in SQL ?
Answer :- SELECT 13*15;
4. Is NULL value the same as 0 (zero) ? Write the reason for your answer.
Answer :- No. NULL value is a legal empty value in SQL, it is use to represent NO VALUE.
5. Write the UPDATE command to increase the commission (Column name : COMM) by 500 of all the Salesmen who have achieved Sales (Column name : SALES) more than 200000. The table’s name is COMPANY.
Answer :- UPDATE COMPANY SET COMM = COMM + 500 WHERE SALES > 200000;
6. While using SQL pattern matching, what is the difference between (underscore) and ‘%’ wildcard symbols ?
Answer :- In pattern matching, underscore _ and percent % wildcard symbols are used for pattern matching. Underscore ( _ ) symbol is use in place of one unknown character. Percent % symbol is use in place of any numbers of unknown characters.
Example : SELECT * FROM COMPANY WHERE NAME LIKE “_a%”;
7. Write one similarity and one difference between CHAR and VARCHAR data types. [CBSE OD 15]
Answer :- : Similarity: CHAR and VARCHAR are used to store alphanumeric data.
Difference: CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type.
8. Write SQL statement to display :-
Today, the date is <current date>
Answer :- : SELECT CONCAT(“Today, the date is “, curdate());
9. Write a command to add a NOT NULL constraint on FEES column of a student table.
Solution : ALTER TABLE student MODIFY FEES DECIMAL(10, 2) NOT NULL;
10. What is a constraint ? Name some constraints that you can apply to enhance database integrity.
Solution : Constraint is a rule or condition applied to the column and table to validate the data.
Some constraints that can apply to enhance database integrity are Primary Key, Foreign Key, Unique, Not Null, Check, Default.
11. What is the primary key? What is a PRIMARY KEY constraint?
Solution :
12. What is NOT NULL constraint? What is a DEFAULT constraint?
Solution :
13. When a column’s value is skipped in an INSERT command, which value is inserted in the database?
Solution : Either NULL or DEFAULT Value (if given)
14, Write Mysql command to display the list of existing databases. [CBSE D 12]
Solution : SHOW DATABASES;
15, Write MySql command will be used to open an already existing database “comcrs”, [CBSE OD 11]
Solution : USE COMCRS;
SOLVED ASSIGNMENT
1. What is SQL? What are different categories of commands available in SQL?
Solution : In order to access data within the Oracle database, all programs and users must use, Structured Query Language (SQL). SQL is the set of commands that is recognised by nearly all RDBMS.
SQL commands can be divided into following categories:
(a) Data Definition Language (DDL) Commands.
(b) Data Manipulation Language (DML) Commands.
(c) Transaction Control Language (TCL) Commands.
(d) Session Control Commands.
(e) System Control Commands.
2. Differentiate between DDL and DML commands.
Solution : The Data Definition Language (DDL) commands, as the name suggests, allow you to perform tasks related to data definition. That is, through these commands, you can perform tasks like, create, alter and drop schema objects, grant and revoke privileges etc.
The Data Manipulation Language (DML) commands, as the name suggests, are used to manipulate data. That is, DML commands query and manipulate data in existing schema objects.
3. Differentiate between CHAR and VARCHAR datatypes.
Solution : The difference between CHAR and VARCHAR is that offixed length and variable length. The CHAR datatype specifies a fixed length character string. When a column is given datatype as CHAR(n), then MySQL ensures that all values stored in that column have this length i.e., n bytes. If a value is shorter than this length n then blanks are added, but the size of value remains n bytes.
VARCHAR, on the other hand, specifies a variable length string. When a column is given datatype as VARCHAR(n), then the maximum size a value in this column can have is n bytes. Each value that is stored in this column stores exactly as we specify it i.e., no blanks are added if the length is shorter than maximum length n. However, if we exceed the maximum length n, then an error message is displayed.
4. What is the “data type”? What are main objectives of datatypes?
Solution : Data type is defined as a set of values along with the operations that can be performed on those values.
Some common data types are Integer, Float, Varchar, Char, String, etc.
Main objectives of datatypes are :
- Optimum usage of storage space
- Represent all possible values
- Improve data integrity
5. (a) Write two examples of DBMS software.
(b) What is meant by NULL value in MySQL?
(c) Table Club’ has 4 rows and 3 columns. Table ‘Member’ has 2 rows and 5 columns. What will be the cardinality of the Cartesian product of them?
Solution : (a) (i) Oracle (ii) SQL Server (iii) MySQL
(b) Null value signifies a legal empty value.
(c) 8
6. Consider the following table named “GARMENT”. Write command of SQL for (i) to (iv).
Table: GARMENT
| GCODE | GNAME | SIZE | COLOUR | PRICE |
| 111 112 113 114 115 116 | TShirt Jeans Skirt Ladies Jacket Trousers Ladies Top | XL L M XL L L | Red Blue Black Blue Brown Pink | 1400.00 1600.00 1100.00 4000.00 1500.00 1200.00 |
(i) To display names of those garments that are available in ‘XL’ size.
(ii) To display codes and names of those garments that have their names starting with ‘Ladies’.
(iii) To display garment names, codes and prices of those garments that have price in the range 1000.00 to 1500.00 (both 1000.00 and 1500.00 included).
(iv) To change the colour of garment with code as 116 to “Orange”.
Solution
(i) SELECT GNAME FROM GARMENT WHERE SIZE = ‘XL’
(ii) SELECT GCODE, GNAME FROM GARMENT WHERE GNAME LIKE ‘Ladies%’ ;
(iii) SELECT GNAME, GCODE, PRICE FROM GARMENT WHERE PRICE BETWEEN l000.00 AND 1500.00;
(iv) UPDATE garment SET COLOUR = ‘orange’ WHERE GCODE = 116;
7. Consider the following table named “SOFTDRINK”. Write commands of SQL for (i) to (iv).
Table: SOFTDRINK
| DRINKCODE | DNAME | PRICE | CALORIES |
| 101 | Lime and Lemon | 20.00 | 120 |
| 102 | Apple Drink | 18.00 | 120 |
| 103 | Nature Nectar | 15.00 | 115 |
| 104 | Green Mango | 15.00 | 140 |
| 105 | Aam Panna | 20.00 | 135 |
| 106 | Mango Juice Bahaar | 12.00 | 150 |
- To display names and drink codes of those drinks that have more than 120 calories.
- To display drink codes, names and calories of all drinks, in descending order of calories.
- To display names and price drinks that have price in the range 12 to 18 (both 12 and 18 included).
- Increase the price of all drinks in the given table by 10%.
Solution
- SELECT DNAME, DRINKCOOE FROM SOFTORINK WHERE CALORIES > 120;
- SELECT DRINKCODE, DNAME, CALORIES FROM SOFTDRINK ORDER BY CALORIES DESC;
- SELECT DNAME, PRICE FROM SOFTDRINK WHERE PRICE BETWEEN 12 and 18;
- UPDATE SOFTDRINK SET PRICE = PRICE + 0.10 * PRICE;
