Length of table/column

What is the maximum length of table name in Oracle?…………………………….30 characters

What is the maximum length of column name in Oracle?………………………….30 characters

Name of table or column always start with alphabet.

SEQUEL

The language, Structured English Query Language (SEQUEL) was developed by IBM Corporation, Inc., to use Dr. E F Codd’s model.

SEQUEL later became SQL (still pronounced “sequel”). In 1979, Relational Software, Inc. (now Oracle) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.

Oracle Datatypes

Oracle supplies the following built-in datatypes:

·Character datatypes

• CHAR
• NCHAR
• VARCHAR2 and VARCHAR
• NVARCHAR2
• CLOB
• NCLOB
• LONG

·NUMBER datatype

The NUMBER(p,s) datatype stores fixed and floating-point numbers.

·Time and date datatypes:

• DATE
• INTERVAL DAY TO SECOND
• INTERVAL YEAR TO MONTH
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

·Binary datatypes

• BLOB
• BFILE
• RAW
• LONG RAW

Summary of Oracle built-in datatypes:

Datatype	Description	Column Length and Default
CHAR (size [BYTE | CHAR])	Fixed-length character data of length size bytes or characters.	Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (single-byte or multibyte) before setting size.
VARCHAR2 (size [BYTE | CHAR])	Variable-length character data, with maximum length size bytes or characters.	Variable for each row, up to 4000 bytes per row. Consider the character set (single-byte or multibyte) before setting size. A maximum size must be specified.
NCHAR (size)	Fixed-length Unicode character data of length size characters.	Fixed for every row in the table (with trailing blanks). Column size is the number of characters. (The number of bytes is 2 times this number for the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 2000 bytes per row. Default is 1 character.
NVARCHAR2 (size)	Variable-length Unicode character data of length size characters. A maximum size must be specified.	Variable for each row. Column size is the number of characters. (The number of bytes may be up to 2 times this number for a the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 4000 bytes per row. Default is 1 character.
CLOB	Single-byte character data	Up to 232 – 1 bytes, or 4 gigabytes.
NCLOB	Unicode national character set (NCHAR) data.	Up to 232 – 1 bytes, or 4 gigabytes.
LONG	Variable-length character data.	Variable for each row in the table, up to 232 – 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
NUMBER (p, s)	Variable-length numeric data. Maximum precision p and/or scale s is 38.	Variable for each row. The maximum space required for a given column is 21 bytes per row.
DATE	Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E.	Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.
INTERVAL YEAR (precision) TO MONTH	A period of time, represented as years and months. The precision value specifies the number of digits in the YEAR field of the date. The precision can be from 0 to 9, and defaults to 2 for years.	Fixed at 5 bytes.
INTERVAL DAY (precision) TO SECOND (precision)	A period of time, represented as days, hours, minutes, and seconds. The precision values specify the number of digits in the DAY and the fractional SECOND fields of the date. The precision can be from 0 to 9, and defaults to 2 for days and 6 for seconds.	Fixed at 11 bytes.
TIMESTAMP (precision)	A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.)The precision value specifies the number of digits in the fractional second part of the SECOND date field. The precision can be from 0 to 9, and defaults to 6	Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.
TIMESTAMP (precision) WITH TIME ZONE	A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as ‘-5:0’, or a region name, such as ‘US/Pacific’.	Fixed at 13 bytes. The default is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
TIMESTAMP (precision) WITH LOCAL TIME ZONE	Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when stored, and adjusted to match the client’s time zone when retrieved.	Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.
BLOB	Unstructured binary data	Up to 232 – 1 bytes, or 4 gigabytes.
BFILE	Binary data stored in an external file	Up to 232 – 1 bytes, or 4 gigabytes.
RAW (size)	Variable-length raw binary data	Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.
LONG RAW	Variable-length raw binary data	Variable for each row in the table, up to 231 – 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
ROWID	Binary data representing row addresses	Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.

Something

Q-1: Which was the first version of Oracle launched in the market? (Ask to Google)

Q-2: Oracle 8i, 9i – i stands for ?? (internet)

Oracle 10g, 11g – g stands for ?? (grid)

Creating Table and Inserting Values in Table:

Creation of Table:

CREATE TABLE <table_name>

            (column_nm datatype(size)[,column_nm datatype(size),……]);

EXAMPLE:

CREATE TABLE EMP(empno number(2),empnm varchar2(15),sal number(6));

 

Insertion of Data into the Table:

INSERT INTO <table_name>(column_nm, column_nm)

            VALUES(expression,expression);

INSERT INTO <table_name> VALUES(&var1,&var2);

 EXAMPLE:

INSERT INTO EMP(empno,empnm,sal) values(1,`Punam’,9000);

INSERT INTO EMP VALUES(1,`Punam’,9000);

INSERT INTO EMP VALUES(&empno,`&empnm’,&sal);

INSERT INTO EMP VALUES(&no,`&nm’,&sal);

INSERT INTO EMP VALUES(&X,`&Y’,&Z);

In SQLPLUS Editor:

SQL> insert into emp(eno,enm,sal) values(&x,’&y’,&z);
Enter value for x: 11
Enter value for y: Pal
Enter value for z: 19000

 NOTE: After the ‘&’ sign it is not compulsory to use only column name to take the user input. Any variable can be used.

 

COMPONENTS OF SQL

COMPONENTS OF SQL

 

SQL commands are divided into five categories:

1. DDL
2. DML
3. DCL
4. TCL
5. DQL

 

1. DDL – Data Definition Language:

DDL statements are used to define the database structure or schema. SQL commands which comes under Data Definition Language (DDL) are :

• CREATE – To create objects in the database
• ALTER – Alters the structure of the database
• DROP – Delete objects from the database
• TRUNCATE – Remove all records from a table, also free the space occupied by those records

 

 

2. DML – Data Manipulation Language:

DML statements are used for managing data within schema objects. SQL commands which comes under Data Manipulation Language (DML) are :

• INSERT – Insert data into a table
• UPDATE – Updates existing data within a table
• DELETE – Deletes all records from a table, the space occupied will remain

3. DCL – Data Control Language:

DCL statements are used to control access to the data and to the database.

• GRANT – Gives user’s access privileges to database.
• REVOKE – Withdraw access privileges given with the GRANT command.

 

4. TCL – Transaction Control

TCL statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT – save the work done.
• SAVEPOINT – it marks a point in a transaction to which you can roll back.
• ROLLBACK – restore database to original since the last COMMIT or SAVEPOINT

5. DQL – Data Query Language

It includes SELECT statement. Using it one can query in database.

• SELECT – It display the records from table.

Download: COMPONENTS OF SQL

Assignment_1 : EMP_DEPT

Assignment – 1 : EMP_DEPT…………………….DOWNLOAD

SYLLABUS of 2620006 Software Lab SQL PLSQL

GUJARAT TECHNOLOGICAL UNIVERSITY
MASTER OF COMPUTER APPLICATIONS (MCA)
Semester: II
(w.e.f. January 2012)
Subject: Software Lab (DBMS : SQL & PL/SQL)
Subject Code: 2620006
SQL
Introduction to SQL, Advantages of using SQL
Database concepts
SQL concepts and tools
The generic SQL Sentence Construct
Create Table
The Create Table Command
Creating a table from a table
Insertion of Data into tables
Inserting of data into a table
Inserting of data into a table from another table
Viewing data in the tables
View all rows and columns
Selected columns and all rows
Select rows and all columns
Selected columns and selected rows
Elimination of duplicates from the select statement
Sorting of data in a table
Delete Operations
Remove of all rows
Removal of a specified row(s)
Update Operations
Updating of all rows
Updating records conditionally
Modifying the structure of tables
Adding new columns
Modifying existing columns
Renaming Tables
Destroying Tables
Examining Objects created by a User
Arithmetic Operators
Logical Operators
Range Searching
Pattern Matching
Column Alias
Aggregate Functions
Scalar Functions
Date Conversion Functions
Data Constraints
Defining integrity constraints in the alter table command
Dropping integrity constraints in the alter table command
Default Value Concept
Grouping Data from tables
Using the WHERE clause with grouped data
Using the HAVING clause with grouped data
Comparison of WHERE and HAVING
Manipulating dates in SQL
To_char, To_Date, Special Date Formats using to_char functions
Subqueries
Joins
Inner Join, Equi Joins, Self-Join, Outer Joins
Union, Intersect and Minus Clause
Index
View
Sequence
SQL QUERIES (Based on DDL statement, constraints, DML statement, SELECT
statement and Views)
Note : In all schemas, create the table with necessary constraints ( PK, FK, Not Null, Unique and
Check constraints) on SQL prompt and then solve the given queries.
SQL Practical List:
1. CUST (custno, custname, addln1, addln2, city, state, phone)
ITEM (itemno, itemname, itemprice, qty_on_hand)
INVOICE (invno, invDate, custno)
INV_ITEM (invno, itemno, qty_used)
1. Create the above four tables along with key constraints.
2. Write an Insert script for insertion of rows with substitution variables and insert
appropriate data.
3. Add a column – “colour” to the Item table.
4. Display the column Item name and Price in sentence form using concatenation.
5. Find the total value of each item (item price * qty).
6. Display the list of customers belonging to “Gujarat” state.
7. Display items with unit price between ` 100 and ` 500.
8. Find the customers from “Lalbaug” city of Ahmedabad and Baroda.
9. Find all the customers whose name starts with the letter ‘P’.
10. Sort all customers alphabetically.
11. Sort all items in descending order by their prices.
12. Display invoice dates as per the format “January 16, 2012”.
13. Find the total, average, highest and lowest unit price of an item.
14. Count number of items ordered in each invoice.
15. Find invoices in which three or more items have been ordered.
16. Display the details of items along with its quantity used (natural join).
17. Use outer join to display items ordered as well as items not ordered so far.
18. Find invoices with ‘screw’ in their item name.
19. Display name of items ordered in invoice number 1001.
20. Find the items that are cheaper than item ‘Gear’.
21. Create a table (namely gujarat_cust) for all Gujarat customer based on existing customer
table.
22. Copy all M.P customers to the table with Gujarat customers.
23. Rename Gujarat_cust table to MP_cust table.
24. Find the customers who are not in Gujarat or M.P.
25. Delete the rows from customer table that are also in MP_cust table.
26. Find the first three items which has the highest price.
27. Create a read only view for items having price less than ` 50.
28. Create a sequence and use that sequence in insert statement which can be used to enter
new items into item table.
2. STUDENT (rollno, name, class, birthdate)
COURSE (courseno, coursename, max_marks, pass_marks)
SC (rollno, courseno, marks)
1. Create the above three tables along with key constraints.
2. Write an Insert script for insertion of rows with substitution variables and insert
appropriate data.
3. Add a constraint that the marks entered should strictly be between 0 and 100.
4. While creating SC table, composite key constraint was forgotten. Add the composite key
now.
5. Display details of student who takes ‘Database Management System’ course.
6. Display the names of students who have scored more than 70% in Computer Networks
and have not failed in any subject.
7. Display the average marks obtained by each student.
8. Select all courses where passing marks are more than 30% of average maximum mark.
9. Display details of students who are born in 1980 or 1982.
10. Create a view that displays student courseno and its corresponding marks.
3. HOSTEL (hno, hname, haddress, total_capacity, warden_nm)
ROOM (hno, rno, rtype, location, no_of_students, status)
CHARGES (hno, rtype, charges)
STUDENT (sid, sname, saddr, faculty, dept, class, hno, rno)
FEES (sid, fdate, famount)
The STATUS field tells us whether the room is occupied or vacant. The charges represent the
term fees to be paid half yearly. A student can pay either the annual fees at one time or the half
yearly fees twice a year.
1. Create the above five tables along with key constraints.
2. Write an Insert script for insertion of rows with substitution variables and insert
appropriate data.
3. Add a check constraint to the room table so that the room type allows the following :
values only – ‘s’ for single, ‘d’ for double, ‘t’ for triple and ‘f’ for four-seater.
4. Display the total number of rooms that are presently vacant.
5. Display number of students who are staying in ‘double’ seated room for each hostel.
4. Display the warden name and hostel address of students of ‘Computer Science’
department.
6. Display the hostel details where single seated or four-seated rooms are vacant.
7. Count total number of ‘medical’ students who live in the hostel.
8. Display hostels, which are totally occupied to its fullest capacity.
9. List details about students who are staying in the double-seated rooms of “Chanakya”
Hostel.
5. Display the total number of students staying in each room type of each hostel.
6. Display details about students who have paid the fees in the month of November 2011.
7. For those hostels where total capacity is more than 300, display details of students
studying in the Science faculty.
8. Display the hostel details where there are at least 10 vacant rooms.
9. Display the details of students who have still not paid their hostel fees.
10. Display those hostels where single-seated room is the costliest.
4. SCREEN (screen_id, location, seating_capacity)
MOVIE (movie_id, movie_name, date_of_release)
CURRENT (screen_id, movie_id, date_of_arrival, date_of_closure)
Value of screen_id must start with letters ‘S’.
Attribute location can be any one of ‘FF’, ‘SF’, or ‘TF’ (First floor, second floor or third
floor).
Date_of_arrival must be less than the date_of_closure.
Solve the following queries based on the above schema:
1. Extract the name of movie which has run the longest in the multiplex so far.
2. Find the average duration of a movie on screen number ‘S4’.
3. Get the details of the movie that closed on date 24-December-2011.
1. Movie “Bodygaurd” was released in the 35th week of 2011. Find out the date of its
release considering that a movie releases only on Friday.
4. Get the full outer join of the relations screen and current.
5. DISTRIBUTOR (dno, dname, daddress, dphone)
ITEM (itemno, itemname, colour, weight)
DIST_ITEM (dno, itemno, qty)
1. Add a column CONTACT_PERSON to the DISTRIBUTOR table with the not null
constraint.
2. Create a view LONDON_DIST on DIST_ITEM which contains only those records where
distributors are from London. Make sure that this condition is checked for every DML
against this view.
3. Display the details of all those items that have never been supplied.
4. Delete all those items that have been supplied only once.
5. List the names of distributors who have an ‘A’ and also a ‘B’ somewhere in their names.
6. Count the number of items having the same colour but not having weight between 20 and
100.
7. Display all those distributors who have supplied more than 1000 parts of the same type.
8. Display the average weight of items of the same colour provided at least three items have
that colour.
9. Display the position where a distributor name has an ‘OH’ in its spelling somewhere after
the fourth character.
10. Count the number of distributors who have a phone connection and are supplying item
number ‘I100’.
11. Create a view on the tables in such a way that the view contains the distributor name,
item name and the quantity supplied.
12. List the name, address and phone number of distributors who have the same three digits
in their number as ‘Mr. Talkative’.
13. List all distributor names who supply either item I1 or I7 or the quantity supplied is more
than 100.
14. Display the data of the top three heaviest ITEMS.
6. WORKER (worker_id, name, wage_per_hour, specialised_in, manager_id)
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)
1. Display the date on which each worker is going to end his presently assigned job.
2. Display how many days remain for each worker to finish his job.
3. Display the STARTING_DATE in the following format – ‘The fifth day of the month of
October, 2011’.
4. Change the status to ‘Complete’ for all those jobs, which started in year 2010.
5. Display details of all those jobs where at least 25 workers are working.
6. Display all those jobs that have already been completed.
7. Find all the jobs, which will begin within the next two weeks.
8. List all workers who have their wage per hour ten times greater than the wage of their
managers.
9. List the names of workers who have been assigned the job of molding.
10. What is the total number of days allocated for packaging the goods for all the workers
together.
11. Which workers receive higher than average wage per hour.
12. Display details of workers who are working on more than one job.
13. List the workers having specialization in “Polishing” and have started their job in
December 2011.
14. Display details of workers who are specialized in the same field as that of
Mr. Cacophonix or have a wage per hour more than that any of the workers.
7. PUBLISHER (publ_id, publ_name, contact_person, contact_addr, contact_phone)
CATEGORY (cat_id, cat_details, max_books, duration)
BOOK_MASTER (book_id, bname, isbn_no, total_copies, publ_id)
MEMBER (member_id, mname, cat_id, mem_ship_dt)
ISSUE (ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)
In the above tables, duration is in years and it stores the membership duration for that category.
1. Change the table design of ISSUE table to add a constraint, which will allow only ‘I’
or ‘R’ to be entered in the ISSUE_RET column, which stores the action whether the
book is being issued or returned.
2. Add a column to the MEMBER table, which will allow us to store the address of the
member.
3. Create a table LIBRARY_USERS which has a structure similar to that of the
MEMBER table but with no records.
4. Give details about members who have issued books, which contains the word
‘DATA’ somewhere in their titles.
5. Display the books that have been issued at the most three times in the year 2011.
6. Display the details of books issued right now that is published by “Pearson”.
7. Display the details of books whose all copies are issued.
8. Display the details of the books that have been issued between 1st December 2011 and
31st December 2011. The result should also contain the details of the members to
whom those books have been issued.
9. Display details of all the staff members who have issued at least two books.
10. Display the details about those publishers whose books are available in more than 100
titles in the library.
11. Delete the details of all those members whose membership has expired.
12. List the details of members who have registered with the library in the last three
months.
13. Display the membership period of each staff member registered.
8. APPLICANT (aid, aname, addr, abirth_dt)
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)
This database is for a common entrance test which can be conducted at a number of centers and
can be taken by an applicant on any day except holidays.
1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its value. For
example, if the applicant id ‘1123’, it should now become ‘A1123’.
2. Display the test center details where no tests will be conducted.
3. Display the details about applicants who have the same score as that of “Jaydev” in
“Oracle Fundamentals”.
4. Display the details of applicants who have appeared for all the tests.
5. Display those tests where no applicant has failed.
6. Display details of entrance test centers which had full attendance between 1st November
and 15th November 2011.
7. Display the details of those applicants who have scored more than the cut-off score in the
tests they have appeared in.
8. Display the average and the maximum score, test wise of the tests conducted at Mumbai.
9. Display the number of applicants who have appeared for each test, test center wise.
10. Display the details about test centers where no tests have been conducted.
11. For tests, which have been conducted between 2-8-2011 and 23-9-2011, show details of
the tests as well as the test centers.
12. List the number of applicants who had appeared in the “Oracle Fundamentals” test at
Chennai in the month of September 2011.
13. Display the details about applicants who appeared for tests in the same month as the
month in which they were born.
14. Display the details about APPLICANTS who have scored the highest in each test, test
center wise.
15. Design a read only view, giving details about applicants and the tests that they have
appeared for.
PL/SQL
Understanding the main features of PL/SQL, PLSQL Architecture, advantages of using
procedures
The Generic PL/SQL Block
PL/SQL
Character Set
Literals
Data Types
Constant
Branching and Loop statements
Operators
Comments
Displaying user messages on the screen
Cursor
Implicit Cursor
Explicit Cursor
Cursor For Loops
Parameterized Cursors
Select for Update Cursors
Cursor Variables
Error Handling
User-Named Exception Handlers
User –Defined Exception Handlers (for I/O validations)
User–Defined Exception Handlers (for Business Rule validations)
Procedures
Functions
Packages
Triggers
PL/SQL Practical List:
1. Competition (Comp_code, Comp_name (Dancing, Painting, GK, etc.) )
Participants (Part_no, Part_name, DOB, Address, EmailID, Contact_number )
Scorecard (Part_no, Comp_code, Judge_no [1, 2, 3], Marks)
Implement the following:
A)
1. Find those participants who have registered both for ‘Dancing’ and ‘Painting’
(Note: Use set operator).
2. Find the average score, scored in each competition event.
B)
Create a PL/ SQL block to prepare report in following format.
Display the score card in the following format, for the Participant whose
ID/ Name should be provided by the user.
Talent Winner 2011 ::: <Participant’s Name>
Competition name Judge1 Judge2 Judge3
——————————————————————————————–
1. Painting
2. Dancing
——————————————————————————————–
Total Marks: _______
——————————————————————————————–
2. Customer (Cust_Id, Cust_Name, Cust_Addr, Cust_City, EmailID,Contact_No)
Magazine (Mag_Id, Mag_Name, Unit_Rate, Type_of_subsciption[weekly, monthly, etc.])
Subscription (Cust_Id, Mag_Id, start_date, end_date)
Implement the following:
A)
1. Create a View that displays Customer name, Magazine name along with its rate
which was subscribed during 01-Sept-2010 to 01-Feb-2011.
2. Find top three magazines having the highest sale during last one month of time.
B)
1. Create a function to return No. of customers in city Gandhinagar who have subscribed
the magazine ‘Outlook’ after August 2010. If no such customer exists, throw a user
defined exception with appropriate message.
2. Create a trigger that is fired after an INSERT statement is executed for the Customer
table. The trigger writes the new customer’s code, name and the sysdate in a table
called Customer_Log.(create the table Customer_Log)
3. Account (ac_no, ac_name, act_type)
Transaction (ac_no, trans_date, tran_type, tran_amount, balance)
Note: Act_type may be ‘S’ for saving or ‘C’ for current and tran_type may be ‘D’ for deposit
or ‘W’ for withdrawal.
Implement the following:
A)
1. Find out those saving transactions that took place between 10th January 2011 and
20th January 2011 and have withdrawn an amount greater than Rs. 50,000.
2. Create a Sequence that can be used to enter new account number into the account
table. Add a new record into Account table using the created sequence.
B)
1. Create a trigger not allowing insertion, deletion or updation on Saturday and before
8:00 AM & after 6:00 PM on Account table.
2. Create a package for the following :
Create a function to return the current balance for a given account number.
4. Supplier (sid, sname, contactnum)
Parts (pid, pname, color, unit rate)
Catalog (sid, pid, qty)
Implement the following:
A)
1. Find the top three Parts been ordered and have the highest sale till date.
2. Find those suppliers who charge more for some part that the average cost of that part.
B)
Create a PL/ SQL block to prepare invoice in following format.
Display the invoice in the following format. Use parameterized cursor.
Invoice ::: <Supplier’s Name>
Part Id Part Name Quantity Unit Price Total Price
—————————————————————————————-
—————————————————————————————-
Total: _______
—————————————————————————————-
5. Sailor (sid, sname, rating (0-10), DOB)
Boat (bid, bname, color)
Reserve (sid, bid, date)
Implement the following:
A)
1. Find the sailor(s) whose birthday fall in a leap year.
2. Find the name of the sailor who has reserved either the red or green colored boat.
B)
1. Create a parameterized cursor to display the sailor details who have reserved any
boat after November 2010. If no record found, throw an user defined exception with
appropriate message.
2. Create a function that get the Boat code from the user. Display the sailor_code who
have reserved this boat code. Raise an exception if no information for boat/sailor
exists.
6. Movie (movie_id, movie_name, date_of_release)
Screen (screen_id, location, max_capacity)
Current (movie_id,screen_id, date_of_arrival, date_of_closure)
Note:
Value of screen_id must with letter ‘S’.
Screen location can by any one of ‘FF’, ‘SF’, and ‘TF’.
Date_of_arrival must be less than Date_of_closure.
Max_capacity attribute should have a value greater than 0.
Implement the following:
A)
1. Find the top three movies which have the highest screened record.
2. Create a View which displays the movie details along with the information about the
screen on which it is currently screened.
B)
1. Create a trigger that is fired after an INSERT statement is executed for the Movie
table. The trigger writes the new movie’s code, movie name and the sysdate in a table
called Movie_Log.(create the table Movie_Log)
2. Create a function that get the Screen Code from the user and displays the movie name
currently screened on it. If the given screen code does not exist, throw a user defined
exception with appropriate message.
7. Employee_master(EmpCode , Emp_Name , Dept_Id, Emp_Address , DOB , Basic_Salary)
Department_master(Dept_Code ,Dept_Name)
Implement the following:
A)
1. Create a View that displays some Employee details such as Employee code,
Employee name, Department Name and their Basic Salary.
2. Find those employees who do not belong to Department D102 or D105. (Note: Use
set operator)
B)
Create a PL/ SQL block to prepare report in the following format:
Display the salary slip for the employee in the following format, whose Employee
Code is provided by the user.
Salary Slip for the month March 2012.
Employee Code: <E102> Employee Name: <John Smith>
Department Name: <Finance>
—————————————————————————————-
Basic Salary DA HRA Medical P.F.
___________________________________________________________
Deductions:
—————————————————————————————-
Total Salary : _____________
Note:
HRA is 15% of basic salary
DA is 30% of basic salary
Medical is 1% of basic salary
P.F. is 10% of basic salary
8. Competition (Comp_code, Comp_name (Dancing, Painting, GK, etc.))
Participants (Part_no, Part_name, DOB, Address, EmailID, Contact_number )
Scorecard (Part_no, Comp_code, Judge_no [1, 2, 3], Marks)
Implement the following:
A)
1. Create a sequence that allows entering new ‘Competition Code’ that must start
with ‘CMP’, whenever an insertion is tried to be done.
2. Find the event names which have scored the maximum score by the each judge in
total.
B)
1. Create a parameterized cursor to display the total score scored by each student
with the competition details, the competition event name have to be supplied as
the parameter. If the given event does not exist, throw a user defined exception
with appropriate message.
2. Create a trigger that checks the ‘Competition Code’ must start with ‘CMP’
whenever an insertion is tried to be done. Raise an user defined exception if the
rule is violated.
9. Customer (Cust_Id, Cust_Name, Cust_Addr, Cust_City, EmailID,Contact_No)
Magazine (Mag_Id, Mag_Name, Unit_Rate, Type_of_subsciption[weekly, monthly, etc.])
Subscription (Cust_Id, Mag_Id, start_date, end_date)
Implement the following:
A)
1. Find those customers who haven’t subscribed ‘PCQuest’ or ‘Chip India’. (Using
set operator).
2. Find top two magazines having the highest sale during last one month.
B)
1. Create a package for the following :
Create a function to return No. of customers in city ‘Ahmedabad’ who have
subscribed the magazine ‘PCQuest’ after July 2010. If no such customer exists,
throw a user defined exception with appropriate message.
2. Create a function, which accept the Magazine Code and return the Magazine’s
name and its rate. If the magazine code does not exist, throw a user defined
exception with appropriate message.
10. Account (ac_no, ac_name, act_type)
Transaction (ac_no, trans_date, tran_type, tran_amount, balance)
Note: Act_type may be ‘S’ for saving or ‘C’ for current and tran_type may be ‘D’ for
deposit or ‘W’ for withdrawal.
Implement the following:
A)
1. Find out those saving transactions that took place between 10th January 2012 and
20th January 2012 and have withdrawn an amount greater than ` 50,000.
2. Create a View that display the account information having a balance greater than
` 1,00,000.
B)
1. Create a trigger not allowing insertion, deletion or updation on Saturday and before
8:00 AM & after 6:00 PM on Account table.
2. After every 6 months all the customers are given 5% interest. So for current date, give
interest of 6% to all the customers whose balance are greater than or equal to 2000
and interest of, on their balance.
11. Supplier (sid, sname, contactnum)
Parts (pid, pname, color, unit rate)
Catalog (sid, pid, qty)
Implement the following:
A)
1. Find those suppliers who haven’t ordered any Parts.
2. Create a View that displays the supplier details who have ordered any item having
unit rate greater than ` 500.
B)
Create a PL/ SQL block to prepare invoice in following format.
Prepare this report Part information wise. Use parameterized cursor.
contd…
Part Details :::
Part Id Part Name Quantity (in Hand) Unit Price
—————————————————————————————-
—————————————————————————————-
Total Parts Available: <Total Count>
12. Sailor (sid, sname, rating (0-10), DOB)
Boat (bid, bname, color)
Reserve (sid, bid, date)
Implement the following:
A)
1. Find the name of the sailor who has not reserved the red colored boat.
2. Find the name of the sailor who is youngest among all.
B)
3. Create a trigger that checks the ‘Boat Code’ must start with ‘B’ whenever an insertion
is tried to be done. Raise a user defined exception if the rule is violated.
4. Create a procedure that get the Sailor Code from the user and check whether that
Sailor was born in a leap year or not. If the given sailor code does not exist, throw a
user defined exception with appropriate message.
13. Movie (movie_id, movie_name, date_of_release)
Screen (screen_id, location, max_capacity)
Current (movie_id,screen_id, date_of_arrival, date_of_closure)
Note:
Value of screen_id must with letter ‘S’.
Screen location can by any one of ‘FF’, ‘SF’, and ‘TF’.
Date_of_arrival must be less than Date_of_closure.
Max_capacity attribute should have a value greater than 0.
Implement the following:
A)
1. Movie ‘Star Wars III’ was released in the 7th week of 2005. Find out the date of its
release considering that a movie releases only on Friday.
2. Get the details of movie that closed on date 15-January-2012.
B)
1. Create a trigger that checks the ‘screen_id’ must start with ‘S’ whenever an insertion
is tried to be done. Raise a user defined exception if the rule is violated.
2. Create a package for the following :
Create a procedure to print Movie Name where Movie code is been supplied by the
user.
14. Patient (Patient_code, Patient_name, Address, City, DOB, Gender)
Implement the following:
A)
1. Create a Sequence that can be used to enter new Patient code into the Patient table.
Add a new record into Patient table using the created sequence using the format
‘PT1001’.
2. Find those patients who do not belong to ‘Ahmedabad’ or ‘Mehsana’ city. (Use set
operator)
B)
Create a PL/SQL block to generate the following report:
Gender (Patients in Age group)
1-20 21-40 41-60 61-80 81-100 Total
Male
Female
Total
15. SubjectMaster (Sub_code, Sub_name)
StudentMaster (Roll_no, Stud_Name, Gender, DOB, Address)
Result (Roll_No, Sub_code, Marks)
Implement the following:
A)
1. Find out the average score in percentage for each subject.
2. Find out the students whose birthday falls into leap year.
B)
Create a PL/SQL block to generate the marksheet subject wise according to the following
format:
100-90 90-80 80-70 70-60 60-50 <50
——————————————————————————————–
Sub Code:
Sub Name:
——————————————————————————————–
Total (in each group):
——————————————————————————————–
16. Book_catalog (book_code, title, Publisher_Name, Category_Name,
yr_of_release, total_copies )
Member (member_code, member_name,mem_ship_dt)
Issue (Issue_id, member_code, book_code, issu_ret, issue_date, issue_ret_dt)
Note:
Add a constraint to Issue table, which will allow only ‘I’ or ‘R’ to be entered in the
ISSUE_RET column, which stores the action whether the book is being issued or
returned.
Implement the following:
A)
1. Find the book details which are currently issued to the members and have crossed the
return date, get details starting with the current date.
2. How many members have registered in the last three months ? Display their details.
B)
1. Create a function which provides the total number of copies available for the issue for
a given book. Book Code to be provided by the user.
2. Create a package for the following:
Create a function to print the book title when Book code is been supplied by the user.
17. Item_master(Item_Cd, Item_Name, Item_Price)
Item_received( Item_Cd, Month, Year, Day, Rec_Qty)
Item_stock(Item_Cd, Month,Year, Open_Stock, Rec_Qty, Close_Stock)
Implement the following:
A)
1. Create a sequence that can be used to enter new items into item table.
2. List items whose range lies between ` 250 and ` 500.
B)
1. Write triggers that affect Item_stock table for the insert, update and delete on
Item_received table.
2. Write a procedure to accept Item Name as input. If it exists, then display the Item
Price otherwise display the proper message through the use of exception.
18. Team Master (Team_Id, Team_Name)
Player Master (Team_Id, Player_Id, Player_Name, Bt_dt)
Bowler (Team_Id, Bowler_Id, Over, Maiden, Run, Wicket)
Batsman (Team_Id, Player_Id, Score, Out_type, Baller_Id, Bteam_Id)
Extra_run(Team_Id, Wide_Run, No_run, Bye_Run, Legbye_Run)
Implement the following:
A)
1. Display the detail of player who has highest score.
2. Display the age of each player in ‘India’ Team.
B)
Write a procedure to display score board of the given team name in proper format.
19. Dept_master (Dept_Id, Dept_Name)
Course_master (Dept_Id, Course_Id, Course_Name)
Strength_Master (Dept_Id, Course_Id, Max_Stud_Allow)
Stud_Det (Dept_Id, Course_Id, Stud_No, Stud_Name)
Implement the following:
A)
1. Display the department & course where maximum students registered.
2. Select name, department & course of students whose names begin with ‘A’.
B)
Create a package which contains the following procedures.
1. Create a Procedure which takes Department name as an argument and returns the
courses in that department and Maximum student allow in that course.
2. Create a Function which takes Department name and Course name as an argument
and return the total number of students registered in that department for that course
20. ItemMaster (Item_Cd, Item_Name, It_Stock, Item_Unit, Item_Price)
CustMaster (Cust_Code, Cust_Name, Cust_Addr, Due_Amount)
Bill Master (Bill_No, Bill_Date, Cust_Code)
BillTran (Bill_No, Item_Cd, Item_Qty)
Implement the following:
A)
1. Display customer details whose due amount is in between ` 5000 to ` 20,000.
2. Select the items whose price is below the Rs. 500.
B)
For given bill number generate the following report.
Customer : <code> Bill No : <no>
<name> Bill Date : <date>
<address>
Sr#. Item Name Item Qty Item Unit Item Price Amount
1.
2.
3.
Total Amount:
21. Empmaster (Emp_No, Emp_Name, Basic)
Holidays (Month, Year, No. of Weekly Off, No. of Holidays)
EmpTran ( Emp_No, Month, Year, Presence Days, Loan Amount)
Note: 1. HRA is 20% of basic salary
2. DA is 45% of basic salary
2. Medical is 5% of basic salary
3. P.F. is 4% of basic salary
4. Salary is given for (Attendance + Holidays + weekly off) days
Implement the following:
A)
1. Add a column Emp_Address to the Empmaster table with the not null constraint.
2. Delete the records of last two years from the current date.
B)
An organization want to print the pay slips in following format for given Employee
Name, Month & Year.
Month : Issue Date:
Year : Days in Month:
____________________________________________________________________
Employee No: Employee Name:
Presence : Holidays : Absence :
Salary Days:
Earnings Deductions
======= ========
Basic : P.F. :
Medical : Loan :
H.R.A. : Prof. Tax : ` 80
D.A. :
Total Earning: Total Deduction:
Total Amount to pay: _____________
22. Student (Stud_Id, Stud_Name, Address, Date of Birth)
Stud-Edu (Stud_Id, Degree Name, Year of Passing, Percentage, Grade)
Implement the following:
A)
1. Display the students whose age is more than 24 years.
2. Display the data of top 3 students in MCA 2011.
B)
1. Write a PL/SQL block to display the detail of students who have done MCA.
2. Write a procedure to accept stud-id as input and handle user-defined exception when
no data found.
23. Weather (City_Id, Name of city, Temperature, Humidity)
Implement the following:
A)
1. Create a sequence that can be used to enter new city into weather table.
2. Display the detail of city whose name starts with ‘M’.
B)
1. Write a function which accepts the name of city & returns the Temperature &
Humidify. Also handle an exception if name of city does not exist.
2. Write a trigger before update on weather for each row if new
temperature > 50 then give the message otherwise update the value.
24. Item Master (Item Code, Item Name, Price, Unit)
Stock (Item Code, Purchased Qty, Sold Qty)
Implement the following:
A)
1. Update the Price of Item Code “I3” from `500 to `550.
2. Display the top 3 costly items.
B)
1. Write a trigger before update for each row not allowing to update if Sold
Qty > Purchased Qty.
2. Write a procedure to insert the new record in Item Master table. If Item is already
exist than raise the exception.
25. Employee Master (Emp_Code, Emp_Name, Birth_Date)
Department Master (Dept_Code, Dept_Name, Budget)
Salary (Dept_Code, Emp_Code, Salary)
Implement the following:
A)
1. Count the number of employee in each department.
2. Create a view to display employee name & its salary.
B)
1. Write a trigger before insert new row into salary table for constraint “Total salary for
department is not exceeding the budget”.
2. Write a function which accept the Employee Name as an argument and return
the salary of that employee. If employee name does not exists than raise the
exception.
Reference Books :
1. “Oracle 9i PL/SQL”, Oracle Press.
2. Ivan Bayross, “SQL, PL/SQL The Programming Language Oracle”.
***** ***** *****