+2] 9. Structured Query Language - Solved Questions from text book

           Chapter 9. Structured  Query Language

(+2 Computer Application / Computer Science , 
Text book Questions and Answers)

1. SQL stands for __________.

Ans. Structured Query Language

 2. Which are the three components of SQL?

 Ans. Data definition language(DDL),Data manipulation language(DML),Data control language(DCL)

 3. SQL can be used to:

 a. create database structures only.

 b. query database data only.

c. modify database data only.

d. All of these can be done by SQL.

Ans d

 4. SQL is: 

a. a programming language.

 b. an operating system.

c. a data sublanguage.

d. a DBMS.

Ans a

 5. Which of the following is not an RDBMS package?

 a. ORACLE

 b. SQL SERVER

c. MySQL

d. HTML

Ans.d

6.. Which of the following commands is used to display the structure of a table?

 a. LIST

 b. SHOW

c. DESCRIBE

 d. STRUCT

Ans.c

 7. Write the syntax of CREATE TABLE command.

CREATE TABLE <table name>( <column name>  <datatype> [<constraints>], [<column name> <datatype> <Constraints>],.............................. .............................. );

8. Name the different column constraints.

•  NOT NULL
•  UNIQUE
• AUTO INCREMENT
• PRIMARY KEY
• DEFAULT       

 9. What is the difference between primary key constraint and unique constraint?

    This constraint declares a column as the primary key of the table. This constraint is similar to UNIQUE constraint except that it can be applied only to one column or a combination of columns. The primary keys cannot contain NULL values. In other words, it can be considered as a combination of UNIQUE and NOT NULL constraints. A PRIMARY KEY constraint is used to enforce a rule that a column should contain only unique, non-NULL data.

10. What are the features of AUTO_INCREMENT constraint?

    MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. If no value is specified for the column with AUTO_INCREMENT constraint, then MySQL will assign serial numbers automatically and insert the newly assigned value in the corresponding column of the new record. By default, the starting value for AUTO_INCREMENT is 1, and it will be incremented by 1 for each new record. This special behavior also occurs if we explicitly assign the value NULL to the column.

 11. Write down the rules for naming a table.

•  The name may contain letters (A - Z, a - z), digits (0 - 9), under score ( _ ) and dollar ($) symbol.

 • The name must contain at least one character. (Names with only digits are invalid).

 • The name must not contain white spaces, special symbols.

 • The name must not be an SQL keyword.

• The name should not duplicate with the names of other tables in the same data base and with other columns in the same table.

12. How many columns in a table can be specified as primary key of the table?

  Ans.1

13. Which of the following is used to add a row into a table?

 a. ADD

b. CREATE

c. INSERT

d. MAKE

Ans. c

14. Which statement is used to insert new data into a table?

 a. ADD RECORD

b. INSERT RECORD

c. INSERT INTO

d. INSERT ROW

Ans. c

15. Write the essential keywords used along with INSERT command.

  INSERT INTO  TABLE NAME [<column1>,<column2>,....<columnn>]  VALUES(<value 1>,<value2>,,..<valuen>);

16.  Name the keyword used with SELECT command to avoid duplication in the values of a column. 

  This duplication can be eliminated using the keyword DISTINCT

17. Which is the essential clause for SELECT query? 

   The name of the table is given with the keyword FROM, which is an essential clause with SELECT command

18. Which of the following operators is used to check for NULL value in a column?

 a. IN

 b. LIKE

 c. IS 

d. NOT 

Ans. c

19. The operator used for checking pattern matching is _________. 

Ans. LIKE operator

20. What is wrong with the following statement? 

SELECT * FROM emp WHERE grade = NULL;

 21. The command that extracts records from a table is ______

 Ans. WHERE clause command.

22. What is the meaning of ORDER BY clause ?

    It is possible to display the records in a specific order - ascending or descending, of some column values? Yes. The result of a query can be sorted in the ascending or descending order by making use of ORDER BY clause. The order is to be specified by using the keyword ASC (for ascending) or DESC (for descending) along with the column name that is used with ORDER BY clause. By default, the display will be in the ascending order.

 23. Which keyword is used for sorting the data in descending order in MySQL?

 a. DESC

 b. ASC

c. SORT

d. MODIFY

Ans a

 24. In which order are the records displayed in the absence of ORDER BY clause.

 25. In SQL, what is the default sort order of the ORDER BY clause?

 Ans. Ascending order(ASC).

26.  List the aggregate functions of SQL.

 SUM():  Total of the values in the column specified as argument.

AVG() :  Average of the values in the column specified as argument.

MIN(): Smallest value in the column specified as argument.

MAX() : Largest of the values in the column specified as argument.

COUNT(): Number of non NULL values in the column specified as argument.

 27. How do COUNT(*) and COUNT(column_name) differ?

   COUNT(*) and it gives the number of records having the value in column . Note that the * (asterisk) symbol stands for the collection of all the columns in the table. So, if there is at least one field in a record, that record will be taken into consideration for COUNT(*).

 COUNT ( column_name)  counts only the non-NULL values in column.

 28. What is the difference between WHERE clause and HAVING clause?

   WHERE clause, which places conditions on individual rows. We can apply conditions to form groups with the help of HAVING clause.This clause is used along with GROUP BY clause. The condition in HAVING clause is applied to form groups of records, not individual rows.

 29. The usage SUM(*) or MAX(*) is invalid. Why?

   SUM(*) and MAX(*)  are invalid  function.SUM( coumn name) and MAX( column name) are the valid one.

30. What will be the result of the following query?

SELECT COUNT(DISTINCT course) FROM student;

   The result of the query is counts the non null values of the distinct course column of a student table

31. The command used to edit the structure of a table is ________.

 Ans.  ALTER TABLE

 32. Restructuring of a column affects the values in it. State whether true or false.

33. How will you remove a column from a table?

      If we want to remove an existing column from a table, we can use DROP clause along with ALTER TABLE command

34. Name the command used to remove a row from a table.

Ans. DELETE

35. The command to remove rows from a table 'CUSTOMER' is:

 a. REMOVE FROM CUSTOMER

b. DROP TABLE CUSTOMER

 c. DELETE FROM CUSTOMER

d. UPDATE CUSTOMER

Ans. c

36. If values for some columns are unknown, how is a row inserted?

 37. Distinguish between CHAR and VARCHAR data types of SQL.

        String is a group of characters. The most commonly used string data types in MySQL are CHARACTER or CHAR and VARCHAR.
 CHAR or CHARACTER: Character includes letters, digits, special symbols etc. The CHAR is a fixed length character data type. The syntax of this data type is CHAR(x), where x is the maximum number of characters that constitutes the data. The value of x can be between 0 and 255. CHAR is mainly used when the data in a column are of the same fixed length and small in size. For example, if we want to store data like 'M' for male and 'F' for female, in the column Gender of a table, it is better to declare that column as of type CHAR.

  VARCHAR(size) : VARCHAR represents variable length strings. It is similar to CHAR, but the space allocated for the data depends only on the actual size of the string, not on the declared size of the column. For example, if we want to store data in the column Name of a table, it is better to declare that column as of type VARCHAR, because the data in the column may contain different number of characters. The length of the string can vary from 0 to 65535 characters (MySQL version dependent)

38. What happens when we use DELETE FROM command without a WHERE clause?

         If the WHERE clause is not used, all the rows in the table will be deleted. The DELETE command removes only records and not the individual field values.

39. What do you mean by NULL value in SQL?

        NULL is a keyword in SQL that represents an empty value. It is important to remember that NULL does not equate to a blank or a zero; it is something else entirely. Though a blank is equal to another blank and a zero is equal to another zero, a NULL is never equal to anything, not even another NULL. Two NULL values cannot be added, subtracted or compared.

40. Which of the following is the correct order of clauses for the SELECT statements? 

a. SELECT, FROM, WHERE, ORDER BY 

b. SELECT, FROM, ORDER BY, WHERE 

c. SELECT, WHERE, FROM, ORDER BY 

d. SELECT, WHERE, ORDER BY, FROM

Ans. a

41. Read the following strings: (i) 'Sree Kumar' (ii) 'Kumaran' (iii) 'Kumar Shanu' (iv) 'Sreekumar'

Choose the correct option that matches with the pattern '%Kumar', when used with LIKE operator in a SELECT statement.

 a. Strings (i) and (ii) only

 b. Strings (i), (iii) and (iv) only 

 c. Strings (i) and (iii) only

  d. All the strings

Ans. b

42. Pick the odd one out and give reason:

a. CREATE

b. SELECT

c. UPDATE

d. INSERT

Ans. a

43. Write any four DML commands in SQL.

   SELECT,UPDATE,INSERT and DELETE

44.  A table is created in SQL with 10 records. Which SQL command is used to change the values in a column of specified rows? Write the format.

 UPDATE command used.

UPDATE SET <TABLE NAME> SET <column name>  = value [<column name>=<value>,.....] [WHERE  <condition>]; 

  45. Distinguish between DISTINCT and UNIQUE in SQL

     This duplication can be eliminated using the keyword DISTINCT.

      example: SELECT DISTINCT course FROM student;

       In the output, there are no duplicate values. If the column used with DISTINCT keyword                   contains more than one NULL value.

  UNIQUE: It ensures that no two rows have the same value in the column specified with this constraint.It is a column constraint.