Scribd
Upload
8 views

SQL _Program_list

The document outlines various SQL operations including Data Definition Language (DDL) for creating and modifying tables, Data Manipulation Language (DML) for managing data within those tables, and Data Control Language (DCL) for managing user privileges. It also covers PL/SQL programming tasks such as writing blocks for mathematical operations, creating cursors, procedures, functions, and triggers. Additionally, it includes instructions for creating views and performing calculations related to movies and customers.

Uploaded by

yashlanjewar370
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views

SQL _Program_list

The document outlines various SQL operations including Data Definition Language (DDL) for creating and modifying tables, Data Manipulation Language (DML) for managing data within those tables, and Data Control Language (DCL) for managing user privileges. It also covers PL/SQL programming tasks such as writing blocks for mathematical operations, creating cursors, procedures, functions, and triggers. Additionally, it includes instructions for creating views and performing calculations related to movies and customers.

Uploaded by

yashlanjewar370
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

1) DDL Statements:

a. Creating table invoice.


b. Inserting Data into table invoice.
c. Retrieving Data from table invoice.
d. Modifying table structure by adding the new field price (number)
e. Modifying table structure by increasing the size of inv_no.
f. Add and Drop Constraints.
i) Add primary key
ii) Drop primary key constraint
iii) Add unique key constraint
iv) Drop unique key constraint
v) Add check constraint

2) DML Statements:
a. Add records in above table.
b. Change inv_no of I12 with ‘I111’
c. Change issue date of customer of cust_id ‘a01’ to ’22-Dec-06’
d. Change movie no of cust_id ‘a05’ to 11
e. Display all movies with issue_date between 01-jan-06 to 01-sep-06
f. Display information for cust_id ‘a01’ and ‘a02’
g. Delete records with inv_no’I11’ from invoice table.
h. Delete records having return_date<=’20-Jan-06’.

3) DCL and TCL Statements:


a. Grant insertion and deletion privileges on invoice table to another user xyz.
b. Revoke the deletion privileges from user xyz.
c. After insertion of 2 records in invoice table create savepoint A.
d. Delete a record and create a savepoint B.
e. Rollback to A.

4) Create a table movie with fields mv_no(number primary key), title (char), type (char),
star(char), price(number), releasedate(date)
a. Add few records in above table.
b. Count total number of customer.
c. Calculate total price of all the movies.
d. Calculate average price of all the movies.
e. Determine the maximum and minimum movies prices and rename the title as
MAX_PRICE and MIN_PRICE.
f. Count the number of movies having price greater than or equal to 150.
g. Display all the movie which starts with letter ‘K’.
h. Display total no. of movies released in the current year.

5) Create a view of table invoice with following column inv_no(char primary


key),mv_no(number), cust_id(char)
a. insert and delete records
b. drop view

6) PL/SQL programming
a. Write a PL/SQL block to find largest of 2 numbers.
b. Write a PL/SQL block to find largest of three numbers.
c. Write a PL/SQL block to find Factorial of a number
d. Write a PL/SQL block to display multiplication table from 1 to 10.
e. Write a PL/SQL block to find whether the entered no. is palindrome or not.
f. Write a PL/SQL block to find whether the entered string is palindrome or not.
g. Write a PL/SQL block to reverse the number.
7) Cursor
a. Write a cursor to fetch all rows of table invoice.
b. Write a cursor to display all movies with issue_date between 01-jan-06 to 01-sep-06.
If no record found then use Exception to display proper message.
c. Write a PL/SQL to accept the name and age of student and insert into table STUD
after validating age not less than 18 otherwise raise exception.

8) Create Procedure
a. Create procedure to swap two values.
b. Create procedure to display the cust_id, mv_no and call the procedure through main
program.

9) Create Function
a. Create function to find GCD of two numbers.
b. Create function to find the total price of all the movie start with letter ‘K’ and call the
function through main program.

10) Triggers
a. Create trigger that restricts the user from performing a DML on movie table on
‘Monday’
b. Create trigger to convert all the movie name into the upper case on insertion of each
row.

You might also like