Chapter 14: SQL commands

[Link] of the following attributes can be considered as a choice for primary key?
a) Name b) street c) Roll no d) subject

[Link] is the full form of SQL?

a)Structured Query Language c) Simple Query Language?
b) Structured Query List d)none of these

[Link] is the full form of DDL

a)Dynamic Data Language. c) Data Definition Language
b)Detailed Data Language d) Data derivation language

[Link] does DML stand for?

a)Different Mode Level c)Data Mode Lane
b)Data Model Language d)Data Manipulation Language

5. Which of the following keywords will you use in the following query to display the
unique values of a column dept_name!
Select dept_name from company:
a) ALL c) Distinct
b) From d) Name

6. The clause of select query allows us to select only those rows in the result
satisfy aspecified condition.
a)where c)having
b)from d)like

7. Consider following SQL statement. What type of statement is

this? Select * from employee;
a)DML c) DCL
b)DDL d) Integrating constraint

[Link] term is used to refer to a field in a table

a)Attribte c)Row
b)Tuple d)Instance

9. By default, ORDER by Clause lists the results in order

a)Descending c) Ascending
b)Any d) Both a and b

10 . The keyword eliminates redundant

data
a) SELECT C)DISTINCT
b) WHERE d)None of these
11 The _constraint provides a default value to column when the Insert into
statementdoes not provide a specific value.
a) DEFAULT c) CHECK
b) UNIQUE d) NOT NULL

12. The constraint ensure that all values in a column are distinct
e) DEFAULT b)UNIQUE c)CHECK d)NOT NULL

13. The constraint ensures that all values in a column satisfy certain condition
f) DEFAULT b)UNIQUE C) CHECK d) NOT NULL

14. ------------ is used to identify each row in a table

a) FOREIGN KEY b) UNIQUE c)CHECK d) PRIMARY KEY
15. Which command is used to modify an existing table in SQL?
a) Update b) Change c) Modify d) Alter

16.____________Command is used to delete entire table structure in SQL

a) Cancel b) Quit c) Delete d) Drop

17.__________Keyword is used to eliminate duplicate records.

a) Distinct b) Unique c) Only d) Special

18. Which of the following is not DDL Command

a) Create b) Alter c) Drop d) Delete

19. Which of the following is not a DML Command?

a) Insert b) Select c) Update d) Alter

20.__________Clause is used to arrange the data in ascending order or descending order.

a) Order By b) Group By c) Sort By d) Index By

21._______command is used to provide access permission or privileges on database to

users
a) Revoke b) Rollback c) Commit d) Grant

22. Which of the following is not an SQL Constraint?

a) Not Null b) Default c) Primary Key d) Secondary key

23..____________Command is used to make a new table

a)Insert b)Grant c)Create d) Revoke

24. ..____________Command is used to display the data of a table

a) Select b)View c)Display d)Print.

25. Which of the following is not a logical operator

a) ALL b)ANY c)IN d)ON

26._______command is used to take back the access permission or privileges on

database to users
a) Revoke b) Rollback c) Commit d) Grant

26._______command is used to make changes permanent in Database

a) Revoke b) Rollback c) Commit d) Grant

27._______command is used to undo the transaction changes done on Database

a) Revoke b) Rollback c) Commit d) Grant

28. ..____________Command is used to add the new row in a table

a) Insert b)Grant c)Create d) Revoke
29. ____________command is used to delete the data of a table
a) Create b) Alter c) Drop d) Delete

30 .__________is a table automatically created by Oracle database.

a) Dual b) Create c) Select d) None of the above

1. What are DDL commands? Give axamples.

These commands are used to define the database structure, alter and drop the database.

Ex: CREATE,ALTER and DROP.

2. Write the functions of DDL

 DDL defines the physical characteristics of each record, field data type and size.
 DDL describes scheme and subschema.
 DDL indicates the key of records.
 DDL provides data security measures.
 DDL provides logical and physical data independence

3.. What are DMl commands ? Give axamples.

These commands are used to manipulate,access the data of a database ,it is used to extract
the desired data from the database

Ex :INSERT , SELECT, UPDATE and DELETE.

4. Write the functions of DML

 DML provides data manipulation techniques like insertion , update & delete data
 DML facilitates the use of relationship between the records
 DML provides independence of programming language by supporting higher level
language

5. What are DCl commands ? Give axamples.

These commands are used to provide the security to the data in a database object.

Ex: GRANT & REVOKE.

6. What are TCl commands ? Give axamples.

 It controls the transaction in a database system.i.e it makes the changes permanent or cancels
the changes without affecting the data..

Ex: COMMIT & ROLLBACK

7. Write the syntax of create command with an ex.

Create command is used to create a new table involves naming the table, defining the column
attributes their data type & size.

Syntax: Ex:

Create table tablename Create table student

( (
Columnname 1 datatype1(size), regno number(5),
Columnname 2 datatype2(size), name char(10),
Columnname n datatypen(size) dob date
); );

8. Explain ALTER command with syntax and Example.

An alter command is used to modify a table by adding a new column or changing the data type of
column or deleting the column
Syntax: Ex:
Alter table tablename Alter table Student
Add Add
( (
Columnname1 Datetype1, Total Number (3),
Columnname2 Datetype2 Percentage Number (5,2)
); );

9 Write the syntax of drop command with an ex.

Drop command is used delete the entire table from database permanently.
OR
Is used to remove the table definition along with the data

Syntax Ex:

Drop table tablename; Drop table student;

10. Write the syntax of insert command with an ex.

Insert command is used to add a new row or record to a table.

Syntax:
Insert into tablename values(‘value1’, ‘value2’, ‘value3’,‘valuen’);

Ex:
Insert into student values(101,’anu’,’12-may-20’);
11. Explain the different forms of select command with an ex
.
Select command is used to extract the desired data from a table.

 To retrieve all rows & columns from a table

Syntax: Select * from tablename;

Ex: Select * from student;

 To select a set of columns from a table.

Syntax: Select columnname1, columnname2 from tablename;

Ex: Select regno, name from student;

 To select a set of rows from a table based on a condition.

Syntax: Ex:
Select * from tablename Select * from student
Where condition; where (regno=105);

12. Write the syntax of update command with an ex.

Update command is used to modify the existing rows in a table

Syntax: Ex:
Update Tablename Update student
Set Set
Columnname=value; Percentage=total/6;

13. Write the syntax of Delete command with an ex.

Delete command is used to delete the existing records from the table .

Syntax: Ex:
Delete from tablename ; Delete from student;

14. Write the difference between DROP and DELETE command in SQL.
Drop Delete
Is used to remove the table definition It is used to remove the data in a table
Along with the data
Syntax: Syntax:
Drop table Tablename; Delete From Tablename where(condition);
Ex: Ex:
Drop table student Delete From Student
Where comb=’pcmb’;
[Link] the syntax of Desc(Describe) command with an ex.

It is used to display the name of the table,column name,data type ,size and along with the
constraints.

Syntax:

Desc tablename;

Ex: Desc student;

16. Write the syntax of where clause with an ex.

Where clause is used to extract specific rows that satisfies a condition & displays only the column
specified in the select clause.

Syntax:

Select columnname1, columnname2 from tablename

Where condition;
Ex:
Select regno, name from student
Where (regno=105);

17. Explain order by clause with syntax & Ex.

Order by clause is used to sort the data in ascending order or descending order
Based on one or more column by default it will be arranged in the ascending order

Syntax Ex:
Select * Select *
From table name from student
Where condition where (result=’pass’)
Order by columnname; order by regno;

18. Explain Group By Clause.

It is used to arrange identical data into groups. The GROUP BY clause follows the WHERE Clause in
a SELECT Statement and precedes the ORDER BY clause.

Syntax:
Select * from tablename
Where condition
Group by columnname1
Order by columnname1;
Ex: To display the list of all students in each combination

Select * from student

Group by comb;

[Link] the syntax of Distinct command with an ex.

It is used to eliminate all the duplicate records and fetching only the unique records from the table.

Syntax:
Select distinct columnname from tablename;

Ex:
Select distinct rollno from student;

20. Explain COMMIT command with syntax

COMMIT command is used to save changes invoked by a transaction to the database.

SQL>COMMIT;

21. Explain ROLLBACK command with syntax

ROLLBACK command is used to undo transactions that have not already been saved to the database.

SQL> ROLLBACK;

22. Mention the different data types used in SQL

Number , char, varchar, date, time.

23. List the different dialects of SQL

(i) MS SQL Server using T-SQL

(ii)Oracle using PL/SQL
(III) MS Access version of SQL is called JET SQL(native format)

24. List the different SQL commands

Data Definition Language(DDL)

Data Manipulation Language(DML)

Data Control Language(DCL)

Transaction Control Language(TCL)

5 Marks Questions

1. Explain the different data types in sql

Data Type Description

Number It is used to store a numeric values in a column

Syntax: columnname number(size);
Ex: marks number(3);

Char It is used to store a fixed length of character type in a

column.
Syntax: column name char(size);
Ex: name char(10);

Varchar/varchar2() It is used to store variable length of alphanumeric data in

a column.
Syntax: column name varchar(size);
Ex: address varchar(10);

Date It used to store date in a column.

standard format is dd-month-year
Syntax: column name Date;
Ex: Dob date;

Time It is used to store time in a column

standard format is hours, minutes & seconds

Long it is used to store variable length upto 2gb

Syntax: column name long;
Ex: Description long;

2. Explain various group functions in sql or aggregate functions

Max() This function is used to get the maximum value from a column
Ex: select MAX(CS) from student;
MIN() This function is used to get the minimum value from a column
Ex: select Min(cs) from student;
AVG() This function is used to get the average value from a numerical
column
Ex: select AVG(cs) from student;
 SUM() This function is used to get the sum value from a numerical column
Ex: select SUM(cs) from student;
COUNT() It returns the number of value where expression is not null
Count(*) It returns the number of value in a table including duplicate & null
value
Select count(*) from student where result=’pass’;

3. Explain the arithmetic operators of sql with suitable example

Operators Description Example

a=20,b=10
+ Addition –adds values on either side of the a+b = 30
operator
- Subtraction subtracts right hand operand from a-b= 10
left hand operand
* Multiplication-multiplies values on either side of A*b =200
the operator
/ Division divides left hand operand by right hand a/b=2
operand
% Modulus divides left hand operand by right hand a%b =0
operand & return remainder
4. Explain the relational or comparison operators of sql with suitable example

Operators Description Example

a=20,b=10
= Checks if the value of two operands are equal (a==b) is false
or not, if yes then condition becomes true
!= Checks if the value of two operands are equal (a!=b)is true
or not, if values are not equal then condition
becomes true
< Checks if the value of left operand is less then (a<b) is false
the value of right hand operand
> Checks if the value of left operand is greater (a>b) is true
then the value of right hand operand
<= Checks if the value of left operand is less then (a<=b) is false
or equal to the value of right hand operand
>= Checks if the value of left operand is greater (a>=b) is true
then or equal to the value of right hand
operand
5. Explain the logical operators of sql with suitable example

Operators Description
All The all operator is used to compare a value to all values in another
value set
And The and operator allows the existence of multiple conditions in an sql
statements where clause.
Or The or operator is used to combine multiple conditions in an sql
 statements where clause.
Exist The exist operator is used to search for the presence of a row in a
specified table that meets certain criteria
Not The not operator reverses the meaning of the logical operator

6. Explain character functions in SQL.

Function Description

 Lower( ) convert all letters to lowercase

 Upper( ) convert all letters to uppercase
 Ltrim( ) removes character from the left of the given position
 rtrim( ) removes character from the right of the given position
 length( ) finds the number of characters in the string

14. Explain SQL Constraints.

NULL Value:-A NULL value in a table is a value in a field that appears to be blank.
NOT NULL Value:-It ensures that a column cannot have a null value.
UNIQUE:-It ensures all the values in a column are different.
PRIMARY KEY:-It uniquely identifies each row or record in a data base table.
FOREIGN KEY:-It uniquely identifies each row or record in any other data base table.
DEFAULT:-It provides a default value for a column when none is specified.
CHECK: It ensures that all values in a column satisfy certain condition.