PORTFOLIO/ PRACTICAL FILE: SQL Commands

 Write in A4 size paper (The first page will be as Cover page about student's Information) PORTFOLIO/ PRACTICAL FILE: SQL Commands

CREATE TABLE Command:

This command is used to create a new table or relation. The syntax for this command is:

CREATE TABLE <table name>
(
<column1> <datatype> [constraint] ,
<column1> <datatype> [constraint],
<column1> <datatype> [constraint], 
);

where []=optional

• The keyword CREATE TABLE is followed by the name of the table that you want to create.
• Then within parenthesis, you write the column definition that consists of the column name followed by its data types and optional constraints.
• There can be as many columns as you require.
• Each column definition is separated with a comma (,).
• All SQL statements should end with a semicolon (;).

Example:

CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20),
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);

 

DROP TABLE Command:

This command is used to delete tables.

For example, suppose you want to drop the Teacher table then the command would be:

DROP TABLE Teacher;

 

ALTER TABLE Command:

This command is used to modify the base table definition. The modifications that can be done using this command are:

Adding a column: Suppose we want to add a column Age in the Teacher table. The following command is used to add the column:

ALTER TABLE Teacher ADD Age INTEGER;

Dropping a column: A column can be dropped using this command.

          ALTER TABLE Teacher DROP Dept_No;

DELETE Command:

In order to delete one or more tuples, the DELETE command is used.

If we want to delete the tuple for Teacher with ID=101 the command would be:

DELETE FROM Teacher WHERE Teacher_ID=101;

To delete all the tuples in a table :          DELETE FROM Teacher;

INSERT INTO Command

This command is used to insert a tuple in a relation. We must specify the name of the relation in which the tuple is to be inserted and the values.

• The values must be in the same order as specified during the Create Table command

To insert a tuple in the Teacher table INSERT command can be used as shown below:

INSERT INTO Teacher VALUES (101,”Shanaya”, “Batra”, ‘F’, 50000, ‘1984-08-11’, 1);

Another form of INSERT command is used to insert a tuple in which the ordering of values is done by explicitly specifying the attribute names as shown below:

INSERT INTO Teacher (First_Name, Last_Name, Gender, Teacher_ID, Date_of_Birth, Dept_No, Salary) VALUES (“Shanaya”, “Batra”, ‘F’, 101, ‘1984-08-11’, 1, 50000);

Note that the values entered are ordered with respect to the attributes mentioned.

• If an attribute value is not explicitly specified its DEFAULT value is used.
• If DEFAULT value is also not specified then NULL value is used.

UPDATE Command:

This command is used to update the attribute values of one or more tuples in a table.

For example in the Teacher table, we want to update the Salary of a teacher with Teacher_ID=101 to 55000.

This can be done using the following command:

UPDATE Teacher SET Salary=55000 WHERE Teacher_ID=101;

To increase the salary of a Teacher Shanaya by 5000, the command would be:

UPDATE Teacher SET Salary=Salary+5000 WHERE Teacher_Name=”Shanaya”;

SELECT Command:

The SELECT Command is used to retrieve information from a database. There are various ways in which the SELECT command can be used.

The syntax of SELECT Command is as follows:

SELECT <attribute list>
FROM  <table name>
WHERE <condition>
ORDER BY <column_name> [ASC | DESC] ;

• The attribute list (separated by comma (,)

Use asterisk (*), which means all the attributes.

      SELECT * FROM Teacher ;

Display First_Name, Last_Name of all records.

      SELECT First_Name, Last_Name FROM Teacher ;

WHERE Clause of SELECT Command

To retrieve all the information about the Teacher with ID=101.

SELECT * FROM Teacher WHERE Teacher_ID=101;

To find the names of all teachers earning more than 50000.

SELECT First_Name,Last_Name FROM Teacher WHERE salary > 50000;

To find the names of all teachers earning more than 50000.

SELECT First_Name,Last_Name FROM Teacher WHERE salary > 50000;

To retrieve names of all the teachers starting from letter ‘S’.

SELECT First_Name FROM Teacher WHERE First_Name LIKE “S%”;

Using Wildcards in Query

Wildcards are symbols that represent a character or combination of characters. The base provides two special wildcards.

                *  – The asterisk (*), represents a collection of characters.

                ? – question marks, represents an individual character.

ORDER BY

To sort the result of a query based on some attributes.

This can be achieved by using the clause – ORDER BY followed by the attributes which need to be sorted.

For ascending order the keyword ASC and for descending order the keyword DESC is used. By default, the order is ascending.

To list the names of teachers in alphabetical order.

SELECT First_Name, Last_Name FROM Teacher ORDER BY First_Name, Last_Name;

To list the names of all the Departments in descending order of their names.

SELECT Dept_Name FROM Department ORDER BY Dept_Name DESC;

 

Q1. What do you mean by query in Open Office Base?

Ans. A query is a request to collect specific information from a table or combination of tables.

Q2. In how many ways you can create query in Open Office Base?

Ans. We can create query in Open Office Base by three ways which are :

1.      Create query in Design view

2.      Create query using Wizard

3.      Create query in SQL view

Q3. Name the Query language which is used in Base?

Ans. SQL

Q4. Which command is used to retrieve data from the table?

Ans. Select command is used to retrieve data from the table.

Q5. Name two categories of SQL Commands.

Ans. Two categories of SQL Commands are :

1.      DDL

2.      DML

Q6. Differentiate between DDL and DML Commands.

Ans.

DDL	DML
It stands for Data Definition Language	It stands for Data Manipulation Language
Example : Create, Alter, Drop	Example : Insert, Update, Delete, Select

Q7. Identify the DML Commands from the following :

1.      Create

2.      Alter

3.      Insert

4.      Delete

Ans. Insert and Delete are DML Commands

Q8. __________ is the most commonly used Data Manipulation Language(DML) command.

Ans. Select

Q9. Name two clauses which can be used with Select Command.

Ans. Two clauses which can be used with Select Command are :

1.      Where Clause

2.      Order By Clause

Q10. Write the Select command to display all the records of table “book”.

Ans. Select * from book;

Q11. Write the shortcut to execute query in “Create query in SQL view” of Base.

Ans. F5

Q12. What is the purpose of Where clause in Select Command?

Ans. Where Clause is used to retrieve specific record from the table.

Q13. What is the purpose of Order by clause in Select Command?

Ans. Order by clause is used to arrange the records in ascending or descending order.

Q14. Write the queries for the following table : Emp

Emp_id	Ename	Salary
1	Suman	20000
2	Sanjay	32000
3	Ravi	30000

a. Display the salary of all the employees after incrementing by Rs 1000.

Ans. Select Salary +1000 from Emp;

b. Display the Employee id and salary of all the employees after decreasing by Rs 500.

Ans. Select Emp_id, Salary – 500 from Emp;

c. Display the Name and salary of all the employees after incrementing it as thrice the amount of present salary.

Ans. Select Ename, Salary * 3 from Emp;

d. Display the Employee id, Name and salary of all the employees after decrementing it as half the amount of present salary.

Ans. Select Emp_id, Ename, Salary/2 from Emp;

e. Display the Employee id and Name of all the employees.

Ans. Select Emp_id, Ename from Emp;

Q15. Write the queries for the following table : Student

Admno	Name	Class	House
1001	Sonam	9	Blue
1002	Ravi	10	Yellow
1003	Poonam	10	Green

a. Display the entire table

Ans. Select * from Student

b. Display the list of students whose house color is blue.

Ans. Select * from Student where House = “Blue”

c. Display the admission number of students whose house color is green.

Ans. Select Admno from Student where House = “Yellow”

d. To view records in ascending order of Admission Number.

Ans. Select * from Student order by Admno Asc;

e. Display the records of Class 10 Students.

Ans. Select * from students where Class = 10;

f. Display the class of ‘Ravi’

Ans. Select Class from Student where Name = ‘Ravi’

g. Insert the given record : 1004, “Aman”, 11, “Blue”

Ans. Insert into Student values( 1004, “Aman”, 11, “Blue”)

Q16. Which command is used for the following task in database?

1.      To insert a new record

2.      To modify the existing data.

3.      To delete a record

4.      To display record

Ans.

1.      Insert

2.      Update

3.      Delete

4.      Select

Q17. Write the queries for the following table : Item

Itemno	Iname	Price	Qty
12	Pen	10	17
13	Eraser	5	15
14	Notebook	15	20

a. Write a query to insert a new record of following details

15, “Pencil”, 20, 10

Ans. Insert into Item values(15, “Pencil”, 20, 10)

b. Write a query to display detail of items whose quantity is more than 10.

Ans. Select * from Item where Qty > 10

c. Write a query to change the quantity of Item number 13 to 25.

Ans. Update Item set Qty = 25 where Itemno = 13

d. Display the total amount of each item. The amount must be calculated as the price multiplied by quantity for each item

Ans. Select Price * Qty from Item.

e. Display the name of item whose price is 10.

Ans. Select Iname from Item where price = 10

f. Display all the records in ascending order of price.

Ans. Select * from Item order by Price asc.

g. Identify the Primary key from table Item.

Ans. Itemno

h. Write the suitable data type of field “Iname”.

Ans. Char or Varchar

i. Write a query to increase the price of all items by Rs2.

Ans. Update Item set Price = Price + 2;

j. Write a query to decrease the price of all items by Rs2 whose price is less than 20.

Ans. Update Item set Price = Price – 2 where Price < 20;

Q18. By default, data is arranged in _________ order using ORDER BY clause.

Ans. Ascending Order

Q19. Which clause is used for the following:

a. To display specific record.

b. To display records in a particular order.

Ans.

1.      Where Clause

2.      Order by Clause

Q20. Consider the following table: STUDENT

ADMNO	NAME	GRADE	DOB	MARKS	HOUSE	GENDER
1001	RUPAL	9	10/04/2006	76	GREEN	M
1002	RASHMI	9	08/12/2005	87	RED	F
1003	ARNAV	10	25/05/2004	81	GREEN	M
1004	SUMONA	9	23/08/2005	68	RED	F
1005	ARUN	9	16/07/2005	72	GREEN	M
1006	TIA	9	22/09/2005	91	BLUE	F
1007	ROSHAN	10	26/08/2004	89	BLUE	M

Write SQL Commands:

a) To Display the details of all students of Green House.

Ans. Select * from STUDENT where HOUSE = “GREEN”;

b) To increase the marks by 5 whose ADMNO is 1005.

Ans. Update STUDENT set MARKS = MARKS + 5 where ADMNO = 1005;

c) To display the details of all students whose MARKS are less than 80.

Ans. Select * from STUDENT where MARKS < 80;

d) Display the list of all students in descending order of MARKS.

Ans. Select * from STUDENT order by MARKS;

Q21. Identify any two Column name/Attribute and their data types from a given table: PLAYER

PID	PNAME	RUNS	GENDER	DOB
P101	SACHIN	13000	M	10/04/2001
P102	KAPIL	7000	M	12/02/1998
P103	SAURABH	12000	M	13/04/2001
P104	VIRAT	12500	M	17/03/2005

Ans. Column Name and it’s data types are:

Column Name	Data type
PID	Char
PNAME	VARCHAR

Q22. Consider the following table: EMPLOYEE

EMPID	NAME	SALARY	DOJ	COMM	DEPT	GENDER
1001	ROHAN	7000	10/04/2006	300	SALES	M
1002	RISHU	12000	08/12/2005	100	FINANCE	F
1003	DEVANSH	9000	25/05/2004	500	SALES	M
1004	SUMAN	10000	23/08/2005	300	MARKETING	F
1005	ARYAN	11000	16/07/2005	500	SALES	M
1006	TAMANNA	15000	22/09/2005	200	FINANCE	F
1007	ROHIT	8000	26/08/2004	900	SALES	M

Write SQL Commands :

a) To display the details of all employees of SALES Department.

Ans. Select * from EMPLOYEE where DEPT = “SALES”;

b) To increase the SALARY by 1000 whose EMPID is 1007.

Ans. Update EMPLOYEE set SALARY = SALARY + 1000 where EMPID = 1007;

c) To display the details of all employees whose SALARY is more than 10000.

Ans. Select * from EMPLOYEE where SALARY > 10000;

d) To display the list of all employees in descending order of SALARY.

Ans. Select * from EMPLOYEE order by SALARY DESC;

Q23. Consider the following table: Sales

Sale_Id	Prod_Name	Price	Discount
1101	Laptop	65000	2500
1103	Pen Tab	29500	1000
1105	Desktop	50000	1550
1106	Printer	12000	2000

1.      How many fields and records are there in Sales table?

2.      Write SQL commands for the following:
i. Display Sales ID and price of all products whose discount is more than 1000.
ii. Display the details alphabetically by product name.
iii. Display product name and sales price after deducting the discount from the price.
Note: Sales price can be calculated as (price-discount)

Ans. 1. There are 4 fields and 4 records in table: Book

Ans2 i.) Select Sale_Id, Price from Sales where Discount > 1000;

ii) Select * from Sales order by Prod_Name;

iii) Select Prod_Name, Price- Discount from Sales;

Q24. Consider the following table: Teachers

NUMBER	NAME	AGE	SUBJECT	DATEOFJOIN	SALARY	GENDER
1	JUGAL	34	COMPUTER	10/01/2019	12000	M
2	PRATIGYA	31	SCIENCE	24/03/2019	20000	F
3	SANDEEP	32	MATHS	12/12/2019	30000	M
4	SANGEETA	35	SCIENCE	01/07/2020	40000	F
5	SATTI	42	MATHS	05/09/2020	25000	M
6	SHYAM	50	SCIENCE	27/06/2021	30000	M
7	SHIV OM	44	COMPUTER	25/02/2021	21000	M
8	SHALAKHA	33	MATHS	31/07/2020	20000	F

Write SQL commands:
a. To show all the information about IT teachers.
b. To list the details of all the teachers who are getting salary between 20000 to 35000.
c. To display the subject of all the teachers whose age is more than 40 years.
d. To display the list of names of all the teachers in alphabetical order.

Ans. a. Select * from Teachers where subject = “COMPUTER”;

b. Select * from teachers where Salary >= 20000 and Salary <= 35000;

c. Select Subject from Teachers where Age > 40;

d. Select * from Teachers order by Name;

OR

d. Select Name from Teachers order by Name;