Dept.

of CSE KMCTCEW

CS 333- APPLICATION SOFTWARE LAB

EXP. NO: 1 DDL AND DQL COMMANDS

AIM: To create a database using DDL commands and write DQL queries to retrieve
information from the database.
Q1: Create a table STUDENT with following fields:
Name Data Type
----------------------------------------------------
Rollno number(6) primary key
Name varchar(15)
Dept varchar(4)
City varchar(15)
DOB date NOT NULL
Gender char(1)
SQL> create table student (rollno number(6) primary key,name varchar(15), dept
varchar(4),city varchar(15),dob date not null,gender char(1));
Output: Table Created
Q2: Describe the schema of the table
SQL>desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
NAME VARCHAR2(15)
DEPT VARCHAR2(4)
CITY VARCHAR2(15)
DOB NOT NULL DATE
GENDER CHAR(1)
Q3: Use ALTER TABLE commands to
a) Add the constraint UNIQUE for regno attribute

Application Software Development Lab 1

Dept. of CSE KMCTCEW

alter table studmarks add constraint s unique(regnumber);

Output: Table altered.
b) Add one more column age with NOT NULL constraint
alter table student add(age number(2) not null);
Output: Table altered.
c) Remove the column city from the student table
alter table student drop(city);
Output: Table altered.
d) Modify the data type of regno to varchar(10)
alter table studmarks modify(regnumber varchar(16));
Output: Table altered.
e) Change the name of the attribute DOB to dateofbirth
alter table student rename column dob to dateofbirth;
Output: Table altered
Q4: Rename the table student to stud
rename table student to stud;
Output: Table renamed.
desc student;
ERROR: ORA-04043: object student does not exist
SQL> desc stud;
//write the output obtained
Q5: Drop table stud
Output: Table dropped.
desc stud;
ERROR: ORA-04043: object stud does not exist
Q6: Insert six tuples to the stud table
//Write the query
Q7:Select all students
Select * from student;
//write the output

Application Software Development Lab 2

Dept. of CSE KMCTCEW

Q8: Select name, roll no and gender from student table

Select name, rollno,gender from student;

//write the output
EXP. NO: 2 DML COMMANDS

AIM: Performing DML commands like Insertion, Deletion, Modify and Update records.

Q1: Create following tables:

EMPLOYEE(Eno,Ename,Designation,Dno,Salary)
DEPARTMENT(Deptno,Dname,Location)
Q2: Insert a single record to department table
Q3: insert more than one record into employee table using a single insert command
Q4: update the employee table to set the salary of all employees to Rs.50000 who are all working as teachers
Q5: create a pseudo table for employee with same structure as the table employee and insert rows into the table
using select clause
Q6:select ename, job from employee table
Q7: delete only those who are working as teachers
Q8:List the records in employee table order by salary in ascending order
Q9:display only those employees whose department number is 30
Q10. Display departmentnumber from the employee table by avoiding the duplicate values

EXP NO. : 3 Creating relationship between the databases.

AIM: To make relationship among tables using foreign key referential integrity constraint and write
queries on various types of join operations for combining tables.
When creating a database, we use separate tables for different types of entities. The association
between the entities is specified using relationships between Database tables. These relationships
need to be represented in the database. Normally in Database tables Foreign Keys are used to
reference a column in another table and thereby creating relationship. Also, when fetching data with
SQL, we need to use certain types of JOIN queries to get what we need.

Q1: create a simple customers table:

customers (customer_id ,customer_name )

Create orders table contain a Foreign Key customer id:
orders (order_id,customer_id, amount)
Application Software Development Lab 3
Dept. of CSE KMCTCEW

Q2: Insert rows into both customer and orders table

Q3: Display both customer and orders table
Q4: Perform cross join on customer and orders table

Q4: Perform natural join on customer and orders table

Q4: Perform inner join on customer and orders table

Experiment no: 4:
Implementation of ORDERBY,GROUPBY and HAVING clauses

AIM:-Create the following tables and insert the data and find the result for the queries given below
using SQL.

CUSTOMER
cid- primary key, should start with letter c
name- not null
userid- unique
password- not null

CID NAME DATEOFREG USERID PASSWORD

C1000 Madhav 22-FEB-12 mmmmm mpmpm
C1001 Bobby 13-MAR-12 bbbbb bpbpb
C1002 Catherine 16-APR-12 ccccc cpcpc
C1003 Kale 21-MAY-12 kkkkk kpkpk
C1004 Vijay 10-JUN-12 vvvvv vpvpv

ITEM
Itemcode – primary key

ITEMCODE ITEMNAME UNITPRICE QUANTITY

I100 pen 10 100
I101 pencil 6 50
I102 eraser 4 75
I103 pen 17 100
I104 book 20 60
I105 book 40 80

Application Software Development Lab 4

Dept. of CSE KMCTCEW

CUSTOMERPURCHASE
custid should refer cid in CUSTOMER table
Itemid should refer itemcode in ITEM table
CUSTID ITEMID QTY BILLNO NETPRICE
C1000 I100 10 b1 100
C1001 I101 5 b2 30
C1002 I102 20 b3 80
C1003 I103 10 b4 170
C1004 I104 20 b5 400
C1003 I105 10 b6 50
C1001 I101 20 b7 120
C1000 I100 30 b8 300

1. List the number of items in the item table based on the item name.
2. Get the customerid and name of the customers who registerdbefre 01-JUNE-2012.
3. List the details of customers where name start with ‘c’ and has ‘t’ as the third character.
4. List the different items in the item table.
5. Display the customerid,name and itemid of the customer who have purchased same items.
6. Get the customerid and name of the customer who have purchased more than one items.
7. Display the customerid,name and total purchase amount of customers who have purchase
items.
8. List the name and the unit price of the items purchased by customers.
9. Get the details of item which has minimum unit price.
10. Display the id of customer whose total purchase amount is more than other customer.
Experiment 5:
Built in Functions in SQL
Aim
To write the suitable SQL queries for the following:
1. Get the system date.
2. Get the absolute value of -5.
3. Round 9.5 upwards.
4. Round 10.6 downwards.
5. Find the remainder when 15/6.
6. Find the value of 6^4.
7. Find the square root of 4096.
8. Convert the string ‘INDIA’ to lowercase.
9. Convert the string ‘kerala’ to uppercase.
10. Remove the leading string ‘K’ from string ‘KKochi’.
11. Remove the trailing m from ‘Malayalam’.
12. Remove the leading and trailing m from ‘Malayalam’.

Application Software Development Lab 5

Dept. of CSE KMCTCEW

13. Convert the string ‘abcdeabcde’ to ‘1234512345’.

14. Display ‘is in’ from string ‘Doctor is in’.
15. Find the length of string ‘Welcome to Kerala’.
16. Join the words ‘Happy ‘ and ‘Birthday’ into a single string.
17. Use the appropriate function to limit the number & decimal places to 1 when the input is
198.2345
18. Convert the string “good morning” to “Good Morning”.
19. Given current date and day as input, find the date in next week on same day .

Application Software Development Lab 6