lp45

8/8/2019 lp45

1/58

ASSIGNMENT -1

Q1.what is sql? What are the different types of categories of sql languages &

explain them?

Ans1. SQL (often erroneously referred to as Structured Query Language) ( )is a database computer language designed for managing data in relational

database management systems (RDBMS), and originally based upon

Relational Algebra

Data Definition Language (DDL) Statements Data Manipulation Language (DML) Statements Transaction Control Statements Session Control Statements System Control Statement Embedded SQL Statements

Data Definition Language (DDL) Statements = Data definitionlanguage (DDL) statements let you to perform these tasks:

Create, alter, and drop schema objects Grant and revoke privileges and roles Analyze information on a table, index, or cluster Establish auditing options Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to thespecified object. For example, an ALTER TABLE statement fails if anotheruser has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do notrequire exclusive access to the specified object. For example, you cananalyze a table while other users are updating the table.

Oracle Database implicitly commits the current transaction before and afterevery DDL statement.

Data Manipulation Language (DML) Statements

Data manipulation language (DML) statements access and manipulate datain existing schema objects. These statements do not implicitly commit thecurrent transaction. The data manipulation language statements are:

CALL

8/8/2019 lp45

2/58

DELETEEXPLAIN PLANINSERTLOCK TABLEMERGE

SELECTUPDATE

The SELECT statement is a limited form of DML statement in that it can onlyaccess data in the database. It cannot manipulate data in the database,although it can operate on the accessed data before returning the results ofthe query.

The CALL and EXPLAIN PLAN statements are supported in PL/SQL only whenexecuted dynamically. All other DML statements are fully supported inPL/SQL.

Transaction Control Statements

Transaction control statements manage changes made by DML statements.The transaction control statements are:

COMMITROLLBACKSAVEPOINTSET TRANSACTION

All transaction control statements, except certain forms ofthe COMMIT and ROLLBACK commands, are supported in PL/SQL. Forinformation on the restrictions, see COMMIT and ROLLBACK.

Session Control Statements

Session control statements dynamically manage the properties of a usersession. These statements do not implicitly commit the current transaction.

PL/SQL does not support session control statements. The session control

statements are:

ALTER SESSIONSET ROLE
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_4010.htm#i2060233http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_9021.htm#i2104635http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_9021.htm#i2104635http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_4010.htm#i2060233

8/8/2019 lp45

3/58

Embedded SQL Statements

Embedded SQL statements place DDL, DML, and transaction control

statements within a procedural language program. Embedded SQL issupported by the Oracle precompilers and is documented in the followingbooks:

Q2.what are different data types in oracle?

Ans the information in a datatypes is mentaines in the form of tables and

each table consists of rows and column , which store data and therefore this

data must have some data must have data type have i.e the type of data ,

which is stored is a table

The different types of data available in oracle are

Data types Description

Varchar2 Cantains variable length text strings

of up to 2000 bytes

Char Contains fixed text strings of up to

255 bytes

Number Contains numeric data

Date Contains date data

Raw Contains binary data of up to 255

bytes

Long Contains text data of up to 2

gigabytes

Long raw Contains binary data of up to

2gigabytes

Rowed Contains disk location for table rows

Blob Large binary object

Clob Large character based object

Nclob Large single or multi-byte character

based object

8/8/2019 lp45

4/58

Bfile Large external file

Q.3what are different commands in each category of sql languages & explainthem?

Ans Defining Database Structures

Data Definition Language, DDL, is the part of SQL that allows a database

user to create and restructure database objects, such as the creation or the

deletion of a table.

Some of the most fundamental DDL commands discussed during following

hours include the following:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

ALTER INDEX

DROP INDEX

CREATE VIEW

DROP VIEW

These commands are discussed in detail during Hour 3, "Managing Database

Objects," Hour 17, "Improving Database Performance," and Hour 20,

"Creating and Using Views and Synonyms."

Manipulating DataData Manipulation Language, DML, is the part of SQL used to manipulate

data within objects of a relational database.

There are three basic DML commands:

INSERT

8/8/2019 lp45

5/58

UPDATE

DELETE

These commands are discussed in detail during Hour 5, "Manipulating Data."

Selecting Data

Though comprised of only one command, Data Query Language (DQL) is the

most concentrated focus of SQL for modern relational database users. The

base command is as follows:

SELECT

This command, accompanied by many options and clauses, is used to

compose queries against a relational database. Queries, from simple to

complex, from vague to specific, can be easily created.

The SELECT command is discussed in exhilarating detail during Hours 7

through 16.

A queryis an inquiry to the database for information. A query is usually

issued to the database through an application interface or via a command

line prompt.

Data Control Language

Data control commands in SQL allow you to control access to data within the

database. These DCL commands are normally used to create objects related

to user access and also control the distribution of privileges among users.

Some data control commands are as follows:

ALTER PASSWORD

GRANT

REVOKE

CREATE SYNONYM

You will find that these commands are often grouped with other commands

and may appear in a number of different lessons throughout this book.

Data Administration Commands

8/8/2019 lp45

6/58

Data administration commands allow the user to perform audits and perform

analyses on operations within the database. They can also be used to help

analyze system performance. Two general data administration commands

are as follows:

START AUDIT

STOP AUDIT

Do not get data administration confused with database

administration. Database administration is the overall administration of a

database, which envelops the use of all levels of commands.Database

administration is much more specific to each SQL implementation than are

those core commands of the SQL language.

Transactional Control Commands

In addition to the previously introduced categories of commands, there are

commands that allow the user to manage database transactions.

COMMIT Saves database transactions

ROLLBACK Undoes database transactions

SAVEPOINT Creates points within groups of transactions in which

to ROLLBACK

SET TRANSACTION Places a name on a transaction

Transactional commands are discussed extensively during Hour 6,

"Managing Database Transactions."

8/8/2019 lp45

7/58

ASSIGNMENT -2

Q1.Create Table employee27 with following attributes:

Employee27(empid(5),Firstname(15),lastname(15),address(50),phoneno(10),

job(10),salary(7))?

Solution 1:

Create table employee27

(empid varchar(5),

Firstname char(15),

Lastname char(15),

Address varchar(50),

Phoneno number(10),

Job char(10),

Salary number(7));

8/8/2019 lp45

8/58

Q2.Enter 10 records in the employee27 table?

Solution 2:

Insert into employee27

values(e100,avanish,goel,rohini,24235453,manager,25000);

Insert into employee27values(e101,ford,kutcher,pitampura,24264565,accountant,30000);


values(e102,amit,aggarwal,rohini,24245453,manager,35000);

Insert into employee27 values(e103,john,smith,paschim

vihar,25554453,clerk,15000);


values(e104,james,stewart,rohini,24675453,accountant,20000);


values(e105,ashok,goel,janakpuri,24543453,engineer,40000);


values(e106,rahul,gupta,vikaspuri,25656673,manager,45000);

8/8/2019 lp45

9/58


values(e107,karan,jindal,rohini,25655663,accountant,30000);


values(e108,vikas,goel,rohini,24256553,manager,35000);

Insert into employee27 values(e109,rakesh,gupta,paschim

vihar,24565463,accountant,45000);

Q3.Describe the structure of employee27 table?

Solution 3:

Desc employee27:

Output:

Name NULL

TYPE

EMPNO VARCHAR(5)

8/8/2019 lp45

10/58

FIRSTNAME CHAR(15)

LASTNAME CHAR(15)

ADDRESS VARCHAR(15)

Q4.Display the name , salary , jobs of all the employees ?

Solution:

Select firstname, lastname, salary , job , from employee27;

Output:

Firstname lastname

job salary

10anish goel

manager 45000

8/8/2019 lp45

11/58

rahul gupta

manager 45000

vikas aggarwaal

manager 45000

Q5.Perform the Followings:

a. Display the name, salary, job of an employee whose empid = e101?

b. Display the name , salary , job of an employee whose firstname is

amit

c. Display the name, salary, job of an employee whose salary is greater

than 25000?

Solution 5:

a. Select firstname, lastname, salary, job from employee27 where empid

= e101;

Output:

Firstname lastname

job salary

avnish goel

manager 45000

8/8/2019 lp45

12/58

b.Select firstname, lastname, salary,job from employee27 where

firstname = amit

Output:

Firstname lastname job salary

Amit goel

manager 45000

c. Select firstname , lastname , job from employee27 where salary > 25000;

Output:

Firstname lastname

job salary

avnish goel

manager 45000

rahul gupta

manager 55000

vikas aggarwaal

manager 35000

ASSIGNMENT -3

Q1.write the syntax of alters, delete and update commands?

Solution:

Alter

1. Adding a new column:

Alter table table_name add(new_column_name datatype(size));

8/8/2019 lp45

13/58

2. Delete an existing column:

Alter table table_name drop column column_name;

3. Increasing size of existing column:

Alter table table_name modify(column_name datatype(size));

Delete

1. To delete all fields:

delete from tablename;

2. To delete with condition:

delete from tablename where condition;

Update

update tablename set column1 = value1 , column2 = value2 where

column3 = value3;

Q.2 Remove the data of an employee whose empid = e102?

Solution:

Delete from employee27 where empid = e102;

8/8/2019 lp45

14/58

8/8/2019 lp45

15/58

Q3.Update the salary of an employee from 45000 to 55000 whose job is

manager ?

Solution:

Update employee27 set salary = 45000 where job = manager;

Output:

Empno Firstname lastname address

job salary

E100 avnish goel rohini

manager 45000

E106 rahul gupta

vikaspuri manager 45000

E108 vikas aggarwaal rohini

manager 45000

8/8/2019 lp45

16/58

Q4.Add a column dept and hire date in the employee table?

Solution:

Alter table employee27 add (dept number (5));

8/8/2019 lp45

17/58

Alter table employee27 add (hiredate);

Output:

Empno Firstname lastname address job

salary Dept hiredate

E100 Avnish Goel Rohini manager

45000

E106 Rahul Gupta Vikaspuri manager

45000

E108 Vikas Aggarwaal Rohini manager

45000

E107 Varun Bagha Rohini clerk

15000

8/8/2019 lp45

18/58

Q5.Enter 10 records for dept and hiredate column?

Solution

Insert into employee27 (dept, hiredates) values (10, 15/Aug/2010);

Insert into employee27 (dept, hiredates) values (20, 24/Jan/2010);

Insert into employee27 (dept, hiredates) values (30, 22/June/2010);

Insert into employee27 (dept, hiredates) values (40, 11/Sept/2010);

Insert into employee27 (dept, hiredates) values (50, 17/March/2010);

Insert into employee27 (dept, hiredates) values (20, 29/April/2010);

Insert into employee27 (dept, hiredates) values (30, 05/May/2010);

Insert into employee27 (dept, hiredates) values (40, 13/July/2010);

8/8/2019 lp45

19/58

Insert into employee27 (dept, hiredates) values (20, 23/February/2010);

Insert into employee27 (dept, hiredates) values (10, 25/Nov/2010);

Q6. Increase the size of a column job to 10?

Solution:

Alter table employee27 modify (job char (10));

Output

8/8/2019 lp45

20/58

Name

Avnish

Rahul

Vikas

Varun

8/8/2019 lp45

21/58

ASSIGNMENT -4

Q1.Display the unique Jobs from the employee27 table?

Solution

Select distinct jobs from employees27;

Output

Jobs

Manager

Manager

Clerk

Assistant

8/8/2019 lp45

22/58

Q2.Display the names of an employee whose First name starts with

character a ?

Solution

Select firstname form employee27 where firstname likea%;

Output:

FirstName

Rahul

Varun

Tarun

8/8/2019 lp45

23/58

Aman

Q3.List the names of employees ending with character s ?

Solution

Select firstname form employee27 where firstname likes%;

Output

8/8/2019 lp45

24/58

FirstName

Vikas

Suhaas

Paras

8/8/2019 lp45

25/58

Q4.List the names of the employees whose name has exactly 5 characters?

Solution

Select firstname from employee27 where firstname like____

Output:

James

Vikaas

Rahul

Karan

8/8/2019 lp45

26/58

Q.5 List the names of employees having a as second character ?

Solution

Select firstname form employee27 where firstname like_a%;

Output:

FirstName

Aakash

8/8/2019 lp45

27/58

Lakshay

Rahul

Sanchit

Q.6 List the names of employees having 2 as in their name ?

8/8/2019 lp45

28/58

Solution:

output

FirstName

Karan

8/8/2019 lp45

29/58

Q7.List the names, jobs, id of an employee whose salary between 25000 to

45000 ?

Solution:

Select firstname, lastname, jobs, empid from employee27 where salary

between 25000 to 45000;

Output:

Firstname lastname

job salary

avnish goel

manager 45000

rahul gupta

manager 45000

8/8/2019 lp45

30/58

vikas aggarwaal

manager 45000

Q8.List the names, id of an employee whose department is either 10 and

20 ?

Solution:

8/8/2019 lp45

31/58

Select firstname, lastname , empid from employee27 where dept as(10,20);

Output

Firstname lastname

empno

avnish goel

e101

rahul gupta

e106

vikas aggarwaal

e109

8/8/2019 lp45

32/58

Q9.list the id of an employee who do not have the name ford, james and

john?

Solution:

Select empid from employee27 where firstname not in (ford, james,

john);

Output:

Empno

E102

E109

E104

E103

8/8/2019 lp45

33/58

E108

Q10.List the names,jobs,id of an employees having hire date between

1/10/2005 to 1/10/2007

Solution

Select firstname, lastname, job, empid from employee27 where hiredate

between 1/Oct/2005 to 1/Oct/2007

Output:

Firstname lastname

8/8/2019 lp45

34/58

job Empno

avnish goel

manager E101

rahul gupta

manager E109

vikas aggarwaal

manager E105

8/8/2019 lp45

35/58

ASSIGNMENT -5

Q1. Display the first name, last name, job, salary of an employee and sort it

using multiple column on the first name and last name?

Solution:

Select firstname, last name, job, salary from employee27 order by firstnamelastname;

Output:

Firstname lastname

job Salary

avnish goel

manager 45000

rahul gupta

manager 45000

vikas aggarwaal

clerk 20000

8/8/2019 lp45

36/58

Q2.Display the names, salary, birthdate of an employee and sort the dates

on the basis of column 3rd ?

Solution:

Select firstname,lastname,hiredate,salary from employee27 order by 3;

Output:

Firstname lastname

Hiredate Salary

avnish goel

8/8/2019 lp45

37/58

13-Nov-09 45000

rahul gupta

10-Dec-09 45000

vikas aggarwaal

16-Jun-10 31000

8/8/2019 lp45

38/58

ASSIGNMENT -6

Q1.Create the customer table where customer id is a primary key , customer

name is not well , customer name is not null , customer address is null ?

Solution:

Create table customer4

(cid varchar(10) primary key,

Cname char(15)not null

Caddress varchar (30)null);

8/8/2019 lp45

39/58

Q2.Create table where department id is a primary key and department name

is a unique key ?

Solution:

Create table department4

(deptno varchar(10) primary key,

Dname char(20)unique key));

8/8/2019 lp45

40/58

8/8/2019 lp45

41/58

Q3.Add a primary key constraint in the employee id of employee id of

employee table ?

Solution:

Alter table employee27 modify (empid varchar(5)primary key);

8/8/2019 lp45

42/58

Q4.Add a foreign key constraint in the dept column of employee4 table ?

Solution:

Alter table employee4 modify (empid varchar(5)foreign key references

department4);

8/8/2019 lp45

43/58

8/8/2019 lp45

44/58

ASSIGNMENT -7

Q1.Display the name , salary of employees and name the table as emp ?

Solution:

Select firstname , lastname , sal from employee4 emp;

8/8/2019 lp45

45/58

Q2.Select the first name of all the employees and nemae the column as

name ?

Solution:

Select firstname name from employee4;

8/8/2019 lp45

46/58

8/8/2019 lp45

47/58

Q3.Display unique department from department table ?

Solution:

Select distinct department from department4;

Output:

Department

France

Hr.

Production

8/8/2019 lp45

48/58

Q4.Display jobs , total salary of an employee on the limits of them jobs ?

Solution:

Select job , sum(sal) from employee4 group by side;

Output:

Sum(salary)

8/8/2019 lp45

49/58

Job

125000 Accountant

15000 manager

14000 manager

8/8/2019 lp45

50/58

Q5.Display the job , maximum salary of an employee whose salary is greater

than 25000 grouped on the basis of jobs and whose salary is greater then

40000

Solution:

Select job , max(sal) from employee27 where sal>25000 group by job having

sal>45000;

Output:

Job

Max(Salary)

Accountant

45000

Manager

45000

8/8/2019 lp45

51/58

ASSIGNMENT -8

Q1.Display the current date ?

Solution:

Select sysdate from dual;

Output:

8/8/2019 lp45

52/58

Sysdate

19-Nov-10

Q2.Display your name with 12(*) Character at the left ?

8/8/2019 lp45

53/58

Solution:

Select lpad(Lakshay,12,(*)) from dual;

Output:

LPAD(lakshay,12,*)

************lakshay

8/8/2019 lp45

54/58

Q3.Display the length of your name ?

Solution:

Select length (lakshay)from dual;

Output:

Length(laksahy)

5

8/8/2019 lp45

55/58

Q4.Dispaly the substring of your name of length 4,starting with 3rd

character ?

8/8/2019 lp45

56/58

Solution:

Select substr(lakshay,3,4) from dual;

Output:

Substringchar

Hayp

8/8/2019 lp45

57/58

Q5.Display the followings:

a)Remove the leading as from string aadsaggfaa ?

b)Remove as from the sides of a string aadsaggfaa?

Solution:

a) Select ltrim(a from aadsaggfaa) from dual;

Output:

Ltrm(aadsaggfaa)

Dsaggf

b) Select ltrim(both , a from aadsaggfaa) from dual;

8/8/2019 lp45

58/58

Output:

Ltrm(aadsaggfaa)

aadsaggf

lp45

Documents