Additional Reading

COMMANDS
 SELECT – Retrieve data from the database

 INSERT – Insert data into a table or Database
 UPDATE – Updates or Modifying existing data within a table
or database
 DELETE – Deletes all records from a table, the space for the
records remain
Some of the important SQL commands are listed below.

 SELECT – This command is used to extracts
data from a database.
 UPDATE – This command is used to update
the data in a database.
 DELETE – This command is used to delete
any data from database.
 INSERT INTO – This command is used to insert a
new data into the database.
 CREATE DATABASE – This command is used to create a
new database.
 ALTER DATABASE – This command is used for any type
of modification in a database.
 CREATE TABLE – This command is used for creating
a new table.
 ALTER TABLE – This command is used for
modification
in the table.
 DROP TABLE – This command is used to delete a
table.
Consider the following table STUDENT.

REGD.NO NAME BRANCH
0001 Ram CSE
0002 Hari MECH
0003 Pradeep EEE
0004 Deepak ETC
Write a SQL command which will show the entire STUDENT
table.
Ans:- The SQL command which will show all the information in
STUDENT table is
SELET * from STUDENT;

REGD.NO NAME BRANCH
0001 Ram CSE
0002 Hari MECH
0003 Pradeep EEE
0004 Deepak ETC
Write down the SQL command which will show the Regd.
No of Pradeep.
The SQL command which will show the Regd.No of Pradeep is
SELECT regd.no from STUDENT WHERE name=Pradeep;

REGD.NO NAME BRANCH
0001 Ram CSE
0002 Hari MECH
0003 Pradeep EEE
0004 Deepak ETC
Write down the SQL command which will show the Name
and Branch column.
The SQL command which will show the NAME and BRANCH
column is
SELECT Name, Branch from STUDENT;

REGD.NO NAME BRANCH
0001 Ram CSE
0002 Hari MECH
0003 Pradeep EEE
0004 Deepak ETC
Write a SQL command which will count the number of rows
existing in STUDENT table.
The SQL command which will count the number of rows present
in STUDENT table is
SELECT COUNT(*) FROM STUDENT;

REGD.NO NAME BRANCH
0001 Ram CSE
0002 Hari MECH
0003 Pradeep EEE
0004 Deepak ETC
I want to add another column in the STUDENT table
as”address”. How can I do that?
To add another column in STUDENT table we can use ALTER
command as
Alter table student add address varchar2(30);
Let us consider table named Employee and with using this table write different SQL
Queries
Query 1 : List the employee whose employee number is 100.

Select * from Employee where employee_Num=100;
Query 2 : List the Employee whose salary is between 50000 to 100000

Select * from Employee where salary between 50000 and 100000;
Select * from Employee where salary >= 50000 and salary <= 100000;
Query 3 : List the Employees whose name starts with ‘Ami’.

Select * from Employees where name like ‘Ami%’;
Query 4 : List the Employees whose name starts with A and surname starts with
S.
Select * from Employees where name like ‘A%’ and surname like ‘S%’;
Query 5 : List the Employees whos surname contains kar word.

Select * from Employees where surname like ‘%kar%’;
Query 6: List the Employees whose name starts with P,B,R characters.
Select * from Employees where name like ‘[PBR]%’;
Query 7: List the Employees whose name not starts with P,B,R characters.
Select * from Employees where name like ‘[!PBR]%’;
Query 8 : What is query to fetch first record from Employee table?
Select * from Employees where rownum=1;

Query 9: What is query to fetch last record from Employees table?
Select * from Employees where rowid = select max(rowid) from Employee;

Query 10 : How to find 2nd highest salary of Employees using Self join?
Select * from Employees a where 2 = select count (distinct salary) from Employee where
a.salary <= b.salary;
Query 11 : What is query to display odd rows from the Employees table?
Answer:
We can achieve this using Mod function,
Select * from(Select rownum as rno,E.* from Employees E) where Mod(rno,2)=1;

Query 11 : What is query to display even rows from the Employees table?
Answer:
We can achieve this using Mod function,
Select * from(Select rownum as rno,E.* from Employees) where Mod(rno,2)=0;

Query 12 : Find Query to get information of Employee where Employee is not assigned to
the department
Select * from Employees where Dept_no Not in(Select Department_no from Employee);
Query 13 : How to Show the Max salary and min salary together from Employees table?
Answer:
Select max (salary) from Employees
Union
Select min (salary) from Employees;

Query 14 : How to get distinct records from the Employees table without using distinct
keyword.
Answer:
Select * from Employees a where rowid = (select max(rowid) from Employees b where
a.Employee_no=b.Employee_no);

Additional Reading

Uploaded by

Copyright:

Available Formats

Additional Reading

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Additional Reading

Uploaded by

Copyright:

Available Formats

COMMANDS

 SELECT – Retrieve data from the database

Some of the important SQL commands are listed below.

Consider the following table STUDENT.

Consider the following table STUDENT.

Consider the following table STUDENT.

Consider the following table STUDENT.

Consider the following table STUDENT.

Query 1 : List the employee whose employee number is 100.

Query 2 : List the Employee whose salary is between 50000 to 100000

Query 3 : List the Employees whose name starts with ‘Ami’.

Query 5 : List the Employees whos surname contains kar word.

Query 8 : What is query to fetch first record from Employee table?

Select * from Employees where rownum=1;

Select * from Employees where rowid = select max(rowid) from Employee;

Select * from(Select rownum as rno,E.* from Employees E) where Mod(rno,2)=1;

Select * from(Select rownum as rno,E.* from Employees) where Mod(rno,2)=0;

Select min (salary) from Employees;

You might also like