Archive
SQL Query Practice
SQL Queries
Create the following Tables:
|
LOCATION |
|
|
Location_ID |
Regional_Group |
|
122 |
NEW YORK |
|
123 |
DALLAS |
|
124 |
CHICAGO |
|
167 |
BOSTON |
|
DEPARTMENT |
||
|
Department_ID |
Name |
Location_ID |
|
10 |
ACCOUNTING |
122 |
|
20 |
RESEARCH |
124 |
|
30 |
SALES |
123 |
|
40 |
OPERATIONS |
167 |
|
JOB |
|
|
Job_ID |
Function |
|
667 |
CLERK |
|
668 |
STAFF |
|
669 |
ANALYST |
|
670 |
SALESPERSON |
|
671 |
MANAGER |
|
672 |
PRESIDENT |
EMPLOYEE |
|||||||||
|
EMPLOYEE_ID |
LAST_NAME |
FIRST_NAME |
MIDDLE_NAME |
JOB_ID |
MANAGER_ID |
HIREDATE |
SALARY |
COMM |
DEPARTMENT_ID |
|
7369 |
SMITH |
JOHN |
Q |
667 |
7902 |
17-DEC-84 |
800 |
NULL |
20 |
|
7499 |
ALLEN |
KEVIN |
J |
670 |
7698 |
20-FEB-85 |
1600 |
300 |
30 |
|
7505 |
DOYLE |
JEAN |
K |
671 |
7839 |
04-APR-85 |
2850 |
NULL |
30 |
|
7506 |
DENNIS |
LYNN |
S |
671 |
7839 |
15-MAY-85 |
2750 |
NULL |
30 |
|
7507 |
BAKER |
LESLIE |
D |
671 |
7839 |
10-JUN-85 |
2200 |
NULL |
40 |
|
7521 |
WARK |
CYNTHIA |
D |
670 |
7698 |
22-FEB-85 |
1250 |
500 |
30 |
Queries based on the above tables:
Simple Queries:
- List all the employee details
- List all the department details
- List all job details
- List all the locations
- List out first name,last name,salary, commission for all employees
- List out employee_id,last name,department id for all employees and rename employee id as “ID of the employee”, last name as “Name of the employee”, department id as “department ID”
- List out the employees anuual salary with their names only.
Where Conditions:
- List the details about “SMITH”
- List out the employees who are working in department 20
- List out the employees who are earning salary between 3000 and 4500
- List out the employees who are working in department 10 or 20
- Find out the employees who are not working in department 10 or 30
- List out the employees whose name starts with “S”
- List out the employees whose name start with “S” and end with “H”
- List out the employees whose name length is 4 and start with “S”
- List out the employees who are working in department 10 and draw the salaries more than 3500
- list out the employees who are not receiving commission.
Order By Clause:
- List out the employee id, last name in ascending order based on the employee id.
- List out the employee id, name in descending order based on salary column
- list out the employee details according to their last_name in ascending order and salaries in descending order
- list out the employee details according to their last_name in ascending order and then on department_id in descending order.
Group By & Having Clause:
- How many employees who are working in different departments wise in the organization
- List out the department wise maximum salary, minimum salary, average salary of the employees
- List out the job wise maximum salary, minimum salary, average salaries of the employees.
- List out the no.of employees joined in every month in ascending order.
- List out the no.of employees for each month and year, in the ascending order based on the year, month.
- List out the department id having atleast four employees.
- How many employees in January month.
- How many employees who are joined in January or September month.
- How many employees who are joined in 1985.
- How many employees joined each month in 1985.
- How many employees who are joined in March 1985.
- Which is the department id, having greater than or equal to 3 employees joined in April 1985.
Sub-Queries
- Display the employee who got the maximum salary.
- Display the employees who are working in Sales department
- Display the employees who are working as “Clerk”.
- Display the employees who are working in “New York”
- Find out no.of employees working in “Sales” department.
- Update the employees salaries, who are working as Clerk on the basis of 10%.
- Delete the employees who are working in accounting department.
- Display the second highest salary drawing employee details.
- Display the Nth highest salary drawing employee details
Sub-Query operators: (ALL,ANY,SOME,EXISTS)
- List out the employees who earn more than every employee in department 30.
- List out the employees who earn more than the lowest salary in department 30.
- Find out whose department has not employees.
- Find out which department does not have any employees.
Co-Related Sub Queries:
47.Find out the employees who earn greater than the average salary for their department.
Joins
Simple join
48.List our employees with their department names
49.Display employees with their designations (jobs)
50.Display the employees with their department name and regional groups.
51.How many employees who are working in different departments and display with department name.
52.How many employees who are working in sales department.
53.Which is the department having greater than or equal to 5 employees and display the department names in ascending order.
54.How many jobs in the organization with designations.
55.How many employees working in “New York”.
Non – Equi Join:
56.Display employee details with salary grades.
57.List out the no. of employees on grade wise.
58.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.
Self Join:
59.Display the employee details with their manager names.
60.Display the employee details who earn more than their managers salaries.
61.Show the no. of employees working under every manager.
Outer Join:
61.Display employee details with all departments.
62.Display all employees in sales or operation departments.
Set Operators:
63.List out the distinct jobs in Sales and Accounting Departments.
64.List out the ALL jobs in Sales and Accounting Departments.
65.List out the common jobs in Research and Accounting Departments in ascending order.
Answers
- SQL > Select * from employee;
- SQL > Select * from department;
- SQL > Select * from job;
- SQL > Select * from loc;
- SQL > Select first_name, last_name, salary, commission from employee;
- SQL > Select employee_id “id of the employee”, last_name “name”, department id as “department id” from employee;
- SQL > Select last_name, salary*12 “annual salary” from employee
- SQL > Select * from employee where last_name=’SMITH’;
- SQL > Select * from employee where department_id=20
- SQL > Select * from employee where salary between 3000 and 4500
- SQL > Select * from employee where department_id in (20,30)
- SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10,30)
- SQL > Select * from employee where last_name like ‘S%’
- SQL > Select * from employee where last_name like ‘S%H’
- SQL > Select * from employee where last_name like ‘S___’
- SQL > Select * from employee where department_id=10 and salary>3500
- SQL > Select * from employee where commission is Null
- SQL > Select employee_id, last_name from employee order by employee_id
- SQL > Select employee_id, last_name, salary from employee order by salary desc
- SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc
- SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc
- SQL > Select department_id, count(*), from employee group by department_id
- SQL > Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id
- SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id
- SQL > Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month
- SQL > Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
- SQL > Select department_id, count(*) from employee group by department_id having count(*)>=4
- SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’jan’
- SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘jan’,’sep’)
- SQL > Select to_char(hire_date,’yyyy’) Year, count(*) from employee group by to_char(hire_date,’yyyy’) having to_char(hire_date,’yyyy’)=1985
- SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
- SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’mar’ group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
- SQL > Select department_id, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’apr’ group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’), department_id having count(*)>=3
- SQL > Select * from employee where salary=(select max(salary) from employee)
- SQL > Select * from employee where department_id IN (select department_id from department where name=’SALES’)
- SQL > Select * from employee where job_id in (select job_id from job where function=’CLERK’
- SQL > Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group=’New York’))
- SQL > Select * from employee where department_id=(select department_id from department where name=’SALES’ group by department_id)
- SQL > Update employee set salary=salary*10/100 wehre job_id=(select job_id from job where function=’CLERK’)
- SQL > delete from employee where department_id=(select department_id from department where name=’ACCOUNTING’)
- SQL > Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))
- SQL > Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)
- SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)
- SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)
- SQL > Select employee_id, last_name, department_id from employee e where not exists (select department_id from department d where d.department_id=e.department_id)
- SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)
- SQL > Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)
- SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id
- SQL > Select employee_id, last_name, function from employee e, job j where e.job_id=j.job_id
- SQL > Select employee_id, last_name, name, regional_group from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id
- SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name
- SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having name=’SALES’
- SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having count (*)>=5 order by name
- SQL > Select function, count(*) from employee e, job j where j.job_id=e.job_id group by function
- SQL > Select regional_group, count(*) from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id and regional_group=’NEW YORK’ group by regional_group
- SQL > Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name
- SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc
- SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc
- SQL > Select e.last_name emp_name, m.last_name, mgr_name from employee e, employee m where e.manager_id=m.employee_id
- SQL > Select e.last_name emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from employee e, employee m where e.manager_id=m.employee_id and m.salary<e.salary
- SQL > Select m.manager_id, count(*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id
- SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id
- SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id and d.department_idin (select department_id from department where name IN (‘SALES’,’OPERATIONS’))
- SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))
- SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union all Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))
- SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’RESEARCH’)) intersect Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’)) order by function
All the Best!!!
Prepared By: Rakesh Hansalia
Frequently asked Database Testing Questions
Frequently asked Database Testing Questions
01) What is Database testing?
An1:
Here database testing means test engineer should test the data integrity, data accessing, query retrieving, modifications, updation and deletion etc
An2:
Database tests are supported via ODBC using the following functions:
SQLOpen, SQLClose, SQLError, SQLRetrieve, SQLRetrieveToFile, SQLExecQuery, SQLGetSchema and SQLRequest.
You can carry out cursor type operations by incrementing arrays of returned datasets.
All SQL queries are supplied as a string. You can execute stored procedures for instance on SQL Server you could use “Exec MyStoredProcedure” and as long as that stored procedure is registered on the SQL Server database then it should execute however you cannot interact as much as you may like by supplying say in/out variables, etc but for most instances it will cover your database test requirements
An3:
Database testing basically include the following.
1) Data validity testing.
2) Data Integrity testing
3) Performance related to database.
4) Testing of Procedure, triggers and functions.
for doing data validity testing you should be good in SQL queries
For data integrity testing you should know about referential integrity and different constraint.
For performance related things you should have idea about the table structure and design.
for testing Procedure triggers and functions you should be able to understand the same.
An4:
Data base testing generally deals with the follwoing:
a)Checking the integrity of UI data with Database Data
b)Checking whether any junk data is displaying in UI other than that stored in Database
c)Checking execution of stored procedures with the input values taken from the database tables
d)Checking the Data Migration .
e)Execution of jobs if any
1. What we normally check for in the Database Testing?
In DB testing we need to check for,
1. The field size validation
2. Check constraints.
3. Indexes are done or not (for performance related issues)
4. Stored procedures
5. The field size defined in the application is matching with that in the db.
3. How to Test database in Manually? Explain with an example
Observing that opertaions, which are operated on front-end is effected on back-end or not.
The approach is as follows :
While adding a record thr’ front-end check back-end that addition of record is effected or not. So same for delete, update,…… Ex:Enter employee record in database thr’ front-end and check if the record is added or not to the back-end(manually).
4. What is data driven test?
An1:
Data driven test is used to test the multinumbers of data in a data-table, using this we can easy to replace the paramerers in the same time from deferent locations.
e.g: using .xsl sheets.
An2:
Re-execution of our test with different input values is called Re-testing. In validate our Project calculations, test engineer follows retesting manner through automation tool.Re-testing is also called DataDriven Test.There are 4 types of datadriven tests.
1) Dynamic Input submissiion ( key driven test) : Sometines a test engineer conducts retesting with different input values to validate the calculation through dynamic submission.For this input submission, test engineer use this function in TSL scriipt– create_input_dialog (“label”);
2) Data Driven Files Through FLAT FILES ( .txt,.doc) : Sometimes testengineer conducts re-testing depends on flat file contents. He collect these files from Old Version databases or from customer side.
3)Data Driven Tests From FRONTEND GREAVES : Some times a test engineer create automation scripts depends on frontend objects values such as (a) list (b) menu (c) table (d) data window (5) ocx etc.,
4)Data Driven Tests From EXCEL SHEET : sometimes a testengineer follows this type of data driven test to execute their script for multiple inputs. These multiple inputs consists in excel sheet columns. We have to collect this testdata from backend tables .
5. How to check a trigger is fired or not, while doing database testing?
It can be verified by querying the common audit log where we can able to see the triggers fired.
6. How to Test Database Procedures and Triggers?
Before testing Data Base Procedures and Triggers, Tester should know that what is the Input and out put of the procedures/Triggers, Then execute Procedures and Triggers, if you get answer that Test Case will be pass other wise fail.
These requirements should get from DEVELOPER
7. Is a “A fast database retrieval rate” a testable requirement?
No. I do not think so. Since the requirement seems to be ambiguous. The SRS should clearly mention the performance or transaction requirements i.e. It should say like ‘A DB retrival rate of 5 micro sec’.
8. How to test a DTS package created for data insert update and delete? What should be considered in the above case while testing it?What conditions are to be checked if the data is inserted, updated or deleted using a text files?
Data Integrity checks should be performed. IF the database schema is 3rd normal form, then that should be maintained. Check to see if any of the constraints have thrown an error. The most important command will have to be the DELETE command. That is where things can go really wrong.
Most of all, maintain a backup of the previous database.
9.How to test a SQL Query in Winrunner? without using DataBase CheckPoints?
By writing scripting procedure in the TCL we can connect to the database and we can test data base and queries.
The exact proccess should be:
1)connect to the database
db_connect(“query1”,DRIVER={drivername};SERVER=server_name;
UID=uidname;PWD=password;DBQ=database_name “);
2)Execute the query
db_excecute_query(“query1″,”write query u want to execute”);
-Condition to be mentioned-
3)disconnect the connection
db_disconnect(“query”);
10. How do you test whether a database in updated when information is entered in the front end?
It depend on your application interface..
1. If your application provides view functionality for the entered data, then you can verify that from front end only. Most of the time Black box test engineers verify the functionality in this way.
2. If your application has only data entry from front end and there is no view from front end, then you have to go to Database and run relevent SQL query.
3. You can also use database checkpoint function in WinRunner.
11. How do you test whether the database is updated as and when an information are added in the front end?Give me an example?
It depends on what level of testing you are doing.When you want to save something from front end obviously, it has to store somewhere in the database
You will need to find out the relevant tables involved in saving the records.
Data Mapping from front end to the tables. Then enter the data from front end and save.
Go to database, fire queries to get the same date from the back end.
12. What steps does a tester take in testing Stored Procedures?
First the tester should to go through the requirement, as to why the particular stored procedure is written for.
Then check whether all the required indexes, joins, updates, deletions are correct comparing with the tables mentions in the Stored Procedure. And also he has to ensure whether the Stored Procedure follows the standard format like comments, updated by, etc.
Then check the procedure calling name, calling parameters, and expected reponses for different sets of input parameters.
Then run the procedure yourself with database client programs like TOAD, or mysql, or Query Analyzer
Rerun the procedure with different parameters, and check results against expected values.
Finally, automate the tests with WinRunner.
13. What are the different stages involved in Database Testing
verify field level data in the database with respect to frontend transactions
verify the constraint (primary key,forien key ….)
verify the performance of the procedures
verify the triggrs (execution of triggers)
verify the transactions (begin,commit,rollback)
14. How to use SQL queries in WinRunner/QTP
in QTP
using output databse check point and database check point ,
select SQL manual queries option
and enter the “select” queris to retrive data in the database and compare the expected and actual
15. What is database testing and what we test in database testing?
An1:
Database testing is all about testing joins, views, imports and exports , testing the procedures, checking locks, indexing etc. Its not about testing the data in the database.
Usually database testing is performed by DBA.
An2:
Database testing involves some in depth knowledge of the given application and requires more defined plan of approach to test the data.
Key issues include:
1) Data Integrity
2) Data Validity
3) Data Manipulation and updates
Tester must be aware of the database design concepts and implementation rules.
An3:
Data bas testing basically include the following.
1)Data validity testing.
2)Data Integritity testing
3)Performance related to data base.
4)Testing of Procedure,triggers and functions.
for doing data validity testing you should be good in SQL queries
For data integrity testing you should know about referintial integrity and different constraint.
For performance related things you should have idea about the table structure and design.
for testing Procedure triggers and functions you should be able to understand the same.
16. What SQL statements have you used in Database Testing?
The most important statement for database testing is the SELECT statement, which returns data rows from one or multiple tables that satisfies a given set of criteria.
You may need to use other DML (Data Manipulation Language) statements like INSERT, UPDATE and DELTE to manage your test data.
You may also need to use DDL (Data Definition Language) statements like CREATE TABLE, ALTER TABLE, and DROP TABLE to manage your test tables.
You may also need to some other commands to view table structures, column definitions, indexes, constraints and store procedures.
17. How to test data loading in Data base testing?
You have to do the following things while you are involving in Data Load testing.
1. You have know about source data (table(s), columns, datatypes and Contraints)
2. You have to know about Target data (table(s), columns, datatypes and Contraints)
3. You have to check the compatibility of Source and Target.
4. You have to Open corresponding DTS package in SQL Enterprise Manager and run the DTS package (If you are using SQL Server).
5. Then you should compare the column’s data of Source and Target.
6. You have to check the number to rows of Source and Target.
7. Then you have to update the data in Source and see the change is reflecting in Target or not.
8. You have to check about junk character and NULLs.
18. What is way of writing testcases for database testing?
An1:
You have to do the following for writing the database testcases.
1. First of all you have to understand the functional requirement of the application throughly.
2. Then you have to find out the back end tables used, joined used between the tables, cursors used (if any), tiggers used(if any), stored procedures used (if any), input parmeter used and output parameters used for developing that requirement.
3. After knowing all these things you have to write the testcase with different input values for checking all the paths of SP.
One thing writing testcases for backend testing not like functinal testing. You have to use white box testing techniques.
An2:
To write testcase for database its just like functional testing.
1.Objective: Write the objective that you would like to test. eg: To check the shipment that i load thru xml is getting inserted for perticular customer.
2.Write the method of input or action that you do. eg: Load an xml with all data which can be added to a customer.
3.Expected :Input should be viewd in database. eg: The shipment should be loaded sucessfully for that customer,also it should be seen in application.4.You can write ssuch type of testcases for any functionality like update,delete etc.
An3:
At first we need to go through the documents provided.
Need to know what tables, stored procedures are mentioned in the doc.
Then test the functionality of the application.
Simultaneously, start writing the DB testcases.. with the queries you have used at the backend while testing, the tables and stored procedures you have used in order to get the desired results. Trigers that were fired. Based on the stored procedure we can know the functionality for a specific peice of the application. So that we can write queries related to that. From that we make DB testcases also.
SQL Definition, SQL Query samples
SQL
- 1. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)
- 2. What operator performs pattern matching?
LIKE operator
- 3. What operator tests column for the absence of data?
IS NULL operator
- 4. Which command executes the contents of a specified file?
START <filename> or @<filename>
- 5. What is the parameter substitution symbol used with INSERT INTO command?
&
- 6. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
- 7. What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution
- 8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
- 9. State true or false. !=, <>, ^= all denote the same operation.
True
- 10. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all
- 11. What command is used to get back the privileges offered by the GRANT command?
REVOKE
- 12. Which system tables contain information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
- 13. Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
- 14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.
- 15. What is the difference between TRUNCATE and DELETE commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
- 16. What command is used to create a table by copying the structure of another table?
Answer :
CREATE TABLE .. AS SELECT command
Explanation :
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
- 17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM(‘!! ATHEN !!’,’!’), ‘!’), ‘AN’, ‘**’),’*’,’TROUBLE’) FROM DUAL;
TROUBLETHETROUBLE
- 18. What will be the output of the following query?
SELECT DECODE(TRANSLATE(‘A’,’1234567890′,’1111111111′), ‘1’,’YES’, ‘NO’ );
Answer :
NO
Explanation :
The query checks whether a given string is a numerical digit.
- 19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
- 20. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN
- 21. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an ‘&’ symbol.
- 22. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
- 23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
- 24. What is the value of ‘comm’ and ‘sal’ after executing the following query if the initial value of ‘sal’ is 10000?
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
sal = 11000, comm = 1000
- 25. What is the use of DESC in SQL?
Answer :
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.
- 26. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
- 27. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR
- 28. What is the output of the following query?
SELECT TRUNC(1234.5678,-2) FROM DUAL;
1200
SQL – QUERIES
I. SCHEMAS
Table 1 : STUDIES
PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)
Table 2 : SOFTWARE
PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)
Table 3 : PROGRAMMER
PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER)
LEGEND :
PNAME – Programmer Name, SPLACE – Study Place, CCOST – Course Cost, DEVIN – Developed in, SCOST – Software Cost, DCOST – Development Cost, PROF1 – Proficiency 1
QUERIES :
- Find out the selling cost average for packages developed in Oracle.
- Display the names, ages and experience of all programmers.
- Display the names of those who have done the PGDCA course.
- What is the highest number of copies sold by a package?
- Display the names and date of birth of all programmers born in April.
- Display the lowest course fee.
- How many programmers have done the DCA course.
- How much revenue has been earned through the sale of packages developed in C.
- Display the details of software developed by Rakesh.
- How many programmers studied at Pentafour.
- Display the details of packages whose sales crossed the 5000 mark.
- Find out the number of copies which should be sold in order to recover the development cost of each package.
- Display the details of packages for which the development cost has been recovered.
- What is the price of costliest software developed in VB?
- How many packages were developed in Oracle ?
- How many programmers studied at PRAGATHI?
- How many programmers paid 10000 to 15000 for the course?
- What is the average course fee?
- Display the details of programmers knowing C.
- How many programmers know either C or Pascal?
- How many programmers don’t know C and C++?
- How old is the oldest male programmer?
- What is the average age of female programmers?
- Calculate the experience in years for each programmer and display along with their names in descending order.
- Who are the programmers who celebrate their birthdays during the current month?
- How many female programmers are there?
- What are the languages known by the male programmers?
- What is the average salary?
- How many people draw 5000 to 7500?
- Display the details of those who don’t know C, C++ or Pascal.
- Display the costliest package developed by each programmer.
- Produce the following output for all the male programmers
Programmer
Mr. Arvind – has 15 years of experience
KEYS:
- SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVIN = ‘ORACLE’;
- SELECT PNAME,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) “AGE”, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) “EXPERIENCE” FROM PROGRAMMER;
- SELECT PNAME FROM STUDIES WHERE COURSE = ‘PGDCA’;
- SELECT MAX(SOLD) FROM SOFTWARE;
- SELECT PNAME, DOB FROM PROGRAMMER WHERE DOB LIKE ‘%APR%’;
- SELECT MIN(CCOST) FROM STUDIES;
- SELECT COUNT(*) FROM STUDIES WHERE COURSE = ‘DCA’;
- SELECT SUM(SCOST*SOLD-DCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = ‘C’;
- SELECT * FROM SOFTWARE WHERE PNAME = ‘RAKESH’;
- SELECT * FROM STUDIES WHERE SPLACE = ‘PENTAFOUR’;
- SELECT * FROM SOFTWARE WHERE SCOST*SOLD-DCOST > 5000;
- SELECT CEIL(DCOST/SCOST) FROM SOFTWARE;
- SELECT * FROM SOFTWARE WHERE SCOST*SOLD >= DCOST;
- SELECT MAX(SCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = ‘VB’;
- SELECT COUNT(*) FROM SOFTWARE WHERE DEVIN = ‘ORACLE’;
- SELECT COUNT(*) FROM STUDIES WHERE SPLACE = ‘PRAGATHI’;
- SELECT COUNT(*) FROM STUDIES WHERE CCOST BETWEEN 10000 AND 15000;
- SELECT AVG(CCOST) FROM STUDIES;
- SELECT * FROM PROGRAMMER WHERE PROF1 = ‘C’ OR PROF2 = ‘C’;
- SELECT * FROM PROGRAMMER WHERE PROF1 IN (‘C’,’PASCAL’) OR PROF2 IN (‘C’,’PASCAL’);
- SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN (‘C’,’C++’) AND PROF2 NOT IN (‘C’,’C++’);
- SELECT TRUNC(MAX(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = ‘M’;
- SELECT TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = ‘F’;
- SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) FROM PROGRAMMER ORDER BY PNAME DESC;
- SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB,’MON’) = TO_CHAR(SYSDATE,’MON’);
- SELECT COUNT(*) FROM PROGRAMMER WHERE SEX = ‘F’;
- SELECT DISTINCT(PROF1) FROM PROGRAMMER WHERE SEX = ‘M’;
- SELECT AVG(SAL) FROM PROGRAMMER;
- SELECT COUNT(*) FROM PROGRAMMER WHERE SAL BETWEEN 5000 AND 7500;
- SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN (‘C’,’C++’,’PASCAL’) AND PROF2 NOT IN (‘C’,’C++’,’PASCAL’);
- SELECT PNAME,TITLE,SCOST FROM SOFTWARE WHERE SCOST IN (SELECT MAX(SCOST) FROM SOFTWARE GROUP BY PNAME);
32.SELECT ‘Mr.’ || PNAME || ‘ – has ‘ || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ‘ years of experience’ “Programmer” FROM PROGRAMMER WHERE SEX = ‘M’ UNION SELECT ‘Ms.’ || PNAME || ‘ – has ‘ || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ‘ years of experience’ “Programmer” FROM PROGRAMMER WHERE SEX = ‘F’;
II . SCHEMA :
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))
MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign key.
QUERIES
- 1. List all the employees who have at least one person reporting to them.
- 2. List the employee details if and only if more than 10 employees are present in department no 10.
- 3. List the name of the employees with their immediate higher authority.
- 4. List all the employees who do not manage any one.
- 5. List the employee details whose salary is greater than the lowest salary of an employee belonging to deptno 20.
- 6. List the details of the employee earning more than the highest paid manager.
- 7. List the highest salary paid for each job.
- 8. Find the most recently hired employee in each department.
- 9. In which year did most people join the company? Display the year and the number of employees.
- 10. Which department has the highest annual remuneration bill?
- 11. Write a query to display a ‘*’ against the row of the most recently hired employee.
- 12. Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
- 13. Find the nth maximum salary.
- 14. Select the duplicate records (Records, which are inserted, that already exist) in the EMP table.
- 15. Write a query to list the length of service of the employees (of the form n years and m months).
KEYS:
- SELECT DISTINCT(A.ENAME) FROM EMP A, EMP B WHERE A.EMPNO = B.MGR; or SELECT ENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);
- SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND DEPTNO=10);
- SELECT A.ENAME “EMPLOYEE”, B.ENAME “REPORTS TO” FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;
- SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);
- SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);
- SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘MANAGER’ );
- SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;
- SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);
- SELECT TO_CHAR(HIREDATE,’YYYY’) “YEAR”, COUNT(EMPNO) “NO. OF EMPLOYEES” FROM EMP GROUP BY TO_CHAR(HIREDATE,’YYYY’) HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY TO_CHAR(HIREDATE,’YYYY’));
- SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) “COMPENSATION” FROM EMP GROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);
- SELECT ENAME, HIREDATE, LPAD(‘*’,8) “RECENTLY HIRED” FROM EMP WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(‘ ‘,15) “RECENTLY HIRED” FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMP);
- SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);
- SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);
- SELECT * FROM EMP A WHERE A.EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(EMPNO)>1) AND A.ROWID!=MIN (ROWID));
- SELECT ENAME “EMPLOYEE”,TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||’ YEARS ‘|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||’ MONTHS ‘ “LENGTH OF SERVICE” FROM EMP;
|
|
Topics
What is PL/SQL and what is it used for?
Should one use PL/SQL or Java to code procedures and triggers?
How can one see if somebody modified any code?
How can one search PL/SQL code for a string/key value?
How can one keep a history of PL/SQL code changes?
How can I protect my PL/SQL source code?
Can one print to the screen from PL/SQL?
Can one read/write files from PL/SQL?
Can one call DDL statements from PL/SQL?
Can one use dynamic SQL statements from PL/SQL?
What is the difference between %TYPE and %ROWTYPE?
What is the result of comparing NULL with NULL?
How does one get the value of a sequence into a PL/SQL variable?
Can one execute an operating system command from PL/SQL?
How does one loop through tables in PL/SQL?
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
What is a mutating and constraining table?
Can one pass an object/table as an argument to a remote procedure?
Is it better to put code in triggers or procedures? What is the difference?
Is there a PL/SQL Engine in SQL*Plus?
Is there a limit on the size of a PL/SQL block?
Where can one find more info about PL/SQL?
What is PL/SQL and what is it used for?
PL/SQL is Oracle’s Procedural Language extension to SQL. PL/SQL’s language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like “Which of the two is the best?” and “Will Oracle ever desupport PL/SQL in favour of Java?”.
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME, TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME, TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME, STATUS FROM USER_OBJECTS WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no “decode” command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven’t cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=… parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDATE” statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
begin EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)'; end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the “EXECUTE IMMEDIATE” statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)'; -- Using bind variables... sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; -- Returning a cursor... sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS cur integer; rc integer; BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE); rc := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END; /
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/
What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
DECLARE v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table’s column. Example:
DECLARE v_EmpNo emp.empno%TYPE;
What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;
How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel
Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is '||emp_rec.ename);
END LOOP;
END LOOP;
END;
/
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP ...do some stuff... COMMIT; END LOOP;
… to …
FOR records IN my_cursor LOOP ...do some stuff... i := i+1; IF mod(i, 10000) THEN -- Commit every 10000 records COMMIT; END IF; END LOOP;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
- Grant direct access on the tables to your user. Do not use roles!
· GRANT select ON scott.emp TO my_user;
- Define your procedures with invoker rights (Oracle 8i and higher);
- Move all the tables to one user/schema.
What is a mutating and constraining table?
“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
- A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
- A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
- Etc.
Can one pass an object/table as an argument to a remote procedure?
The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS BEGIN -- do something with TabX from database B null; END; / -- Database B: sends a PL/SQL table to database A CREATE OR REPLACE PROCEDURE pcalling IS TabX DBMS_SQL.VARCHAR2S@DBLINK2; BEGIN pcalled@DBLINK2(TabX); END; /
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.
Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
SQL> select * from dba_object_size where name = 'procedure_name';
Where can one find more info about PL/SQL?
- Oracle FAQ: PL/SQL code examples
- Oracle FAQ: PL/SQL Books
- PLNet.org – An open source repository for PL/SQL developers
- RevealNet PL/SQL Pipeline – A free community for Oracle developers worldwide
- The PL/SQL Cellar – Free Oracle PL/SQL scripts including a bitwise operations package and message digest algorithms
- PLSolutions.com – PL/Solutions provides consulting and training services for the Oracle PL/SQL language and PL/Vision
- The DMOZ PL/SQL Directory
SQL Basic knowledge
A primary key is a column or set of columns that uniquely identifies the rest of the data in any given row
A foreign key is a column in a table where that column is a primary key of another table, which
means that any data in a foreign key column must have corresponding data in the
other table where that column is the primary key
Data inconsistency: A Data field with same name but info does not match. (Occurs when Data
Redundancy exists)
Data dependency: When application depends on Data structure and has no flexibility.
Data Redundancy: When a data item exists in several files (duplication) (Eliminated if using
Normalized data structure)
Data Independence: Data structures are defined separately from application programs.
Relations: Two-dimensional tables of data values = Table
Atomic: Values cannot be broken down any further.
Domain: Values for attributes are drawn from a domain. Atomic set of attributes.
Ex: Date, City, etc.
Candidate Key: Several keys that act as a subject for primary key.
Concatenated key: Combination of attributes (from candidate keys) that forms the primary key.
Alternate Keys: Candidate keys not chosen to be part of primary key.
Entity integrity: No part of the primary key can be missing. “NOT NULL”
Referential Integrity: A foreign key must have applicable primary key in other table.
Data Warehousing The Fundamentals
Def:Single, complete and consistent store of data obtained from various sources. It is usually made of relational databases. It consists of:
– A set of programs that extract data from an operational environment.
– A database that maintains data warehouse data,
– Systems that provide data to users.
Functions:The main function of a data warehouse is to give end-users faster, easier, and more direct access to corporate data.
Characteristics: – Data Warehouses are offline systems. Their information is not live and it is not continuously updated.
– One of the big advantages of a warehouse implementation is its ability to store historical data.
Codd Rules
In 1985 Codd proposed an informal set of twelve rules by which a database could be evaluated to see how “relational” it is. Very few commercial databases exist which meet or satisfy all twelve rules.
The 12 rules are based on the following foundation rule.
Rule 0:
For any system that is advertised as, or claims to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities. In other words, the DBMS should not have to rely on non-relational methods in order to manage its data. The other twelve rules are all implied in Rule 0, but it is easier to check for the other twelve individually than for this general rule.
Rule 1: The information rule
All information in a relational database is represented explicitly at the logical level and in exactly one way – by values in tables. This includes data about the database itself. Data about the database itself is kept in a data dictionary.
Rule 2: The guaranteed access rule.
Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column value. If a database conforms to rule 2, every atomic value should be easily retrievable. An atomic value is the smallest unit of value in a relational database. In a relational database, an atomic value can always be retrieved if you know the column or attribute name, the table it is stored in, and the primary key’s value.
Rule 3: Systematic treatment of null values
Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in a fully relational DBMS for representing missing information in a systematic way, independent of data type. A null value can mean that data is not there is not known, or is irrelevant. The null value represents empty database fields. There is no value for that field. It is different from zero or blank. A primary field should never have and empty field. This protects the integrity of the database.
Rule 4: Query language
The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data. In a relational database the same query language is used on the data dictionary as is used on the application database.
Rule 5: The comprehensive data sub language rule A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be one language whose statements are expressible, per some well defined syntax, as character strings and that is comprehensive in supporting all the following items: Data definition, view definition, data manipulation, integrity constraints, authorization, transaction boundaries.
There are often many different ways of interacting with the database, for example QBE (Query By Example) or SQL (for more sophisticated queries)
Rule 6: View updating rule
All views that are theoretically updateable are also updateable by the system. A view is a “virtual table” in a database. With a relational DBMS, any change that a user makes to a view should ideally also be made in the base table from which the view is derived.
Rule 7: High-level insert, update and delete
The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data. This means that one command in a relational database should be able to carry out an operation on one or more rows in either a base relation or a view.
Rule 8: Logical integrity
Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.
Ex: moving tables to different disk drives, changing the order of rows in the table, reorganizing database files. In a relational environment the DBMS decides how to access a piece of data.
Rule 9: Data independence
Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairement are made to the base tables. Relational tables may have to be expanded or restructured. New tables may also have to be added to the database. Expansion of a table may involve adding columns to existing tables. The addition of a new column to a table in a relational database should not affect programs that use that table.
Rule 10: Integrity constraints
Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and stored in the catalog, not in the application programs. No data should be stored in a relational database that has not been defined beforehand. Integrity controls must exist to protect the consistency of the database from unauthorized users. Two integrity constraints exist: Entity integrity and referential integrity. Entity integrity states that no part of the primary key can be missing. The key is said to be “not null”. Referential integrity relates to the use of foreign keys. A foreign key is an attribute or group of attributes that matches the primary key of another table. If a table has a foreign key to represent a relationship, then the related table must have a matching primary key.
Rule 11: Extension of rule 8.
A relational DBMS has distribution independence. Distribution independence means that application programs and terminal activities remain unaffected when data distribution is first introduced, when data is redistributed.
Rule 8 requires that data should remain unaffected by the ways in which it is stored. Rule 11 requires that independence should still hold when data is distributed across different locations.
Rule 12: Integrity constraints in the high-level language of the RDBMS. If a relational system has a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher-level relational language (multiple-records-at-a-time). This rule guarantees the integrity constraints contained in the high-level language of the DBMS. In some case, you want to use a one-record-at-a-time procedure. A procedural language such as C, Cobol, or Fortran is used for this. These procedural languages cannot bypass the DBMS.
Data Handling Techniques, DML, DDL, DCL
DML (Data Manipulation Language) [SELECT, INSERT, UPDATE, DELETE]
DDL (Data Definition Language) [CREATE, DROP]
DCL (Data Control Language) [GRANT REVOKE (DBA)]
SELECT Statement examples:
Eg:
SELECT discount, stor_id AS bookstore, discount
FROM discounts
Eg:
OTHER alias examples:
SELECT discount, stor_id bookstore, discount
FROM discounts
Eg:
SELECT discount, bookstore = stor_id, discount
FROM discounts
Eg: With Text:
SELECT ‘The answer is:’ discount, stor_id, discount
FROM discounts
Eg: With Math:
SELECT discount, stor_id, discount, discount*1.75 AS ‘UK VAT’
FROM discounts
Eg: Without repeat:
SELECT DISTINCT state
FROM stores
Eg: The WHERE clause:
SELECT title
FROM titles
WHERE title_id=’MC2222′
Eg: Using the BETWEEN Statement (BETWEEN is inclusive!):
SELECT title_id, qty
FROM sales
WHERE qty BETWEEN 10 AND 30
Eg: Using the IN statement:
SELECT pub_name
FROM publishers
WHERE state IN (‘NH’, ‘MA’)
Eg: The NOT statement:
SELECT pub_name
FROM publishers
WHERE NOT state =’CA’
Eg: The ORDER clause (ASC – ascending [default], DESC – descending)
SELECT au_laname, au_fname
FROM authors
ORDER BY au_laname DESC
Eg: Text matching (% is a wildcard for a string of zero or many characters):
SELECT title_id, title
FROM titles
WHERE title LIKE ‘%ook%’
Eg: Text matching (_ is a wildcard for exactly one character):
SELECT title_id, title
FROM titles
WHERE title LIKE ‘_ook%’
Eg: Text matching ([] is a range wildcard), any titles that start with a,c,d or f:
SELECT title_id, title
FROM titles
WHERE title LIKE ‘[acdf]%’
Eg: Text matching ([^] is the NOT range wildcard), any titles that does not start with a,b,c,d or e:
SELECT title_id, title
FROM titles
WHERE title LIKE ‘[^a-e]%’
UNION, UNION ALL, INTERSECT, MINUS
(To see info from two different tables with same data definition, NO duplication) With
UNION if the tables are seen as ensembles, then the intersection between the tables is listed only once
Eg:
SELECT stor_id, title_id, qty FROM sales_america
UNION
SELECT stor_id, title_id, qty FROM sales_europe
(To see info from two different tables with same data definition, WITH duplication) With UNION ALL If the tables are seen as ensembles, then the intersection between the tables is listed twice
Eg:
SELECT stor_id, title_id, qty FROM sales_america
UNION ALL
SELECT stor_id, title_id, qty FROM sales_europe
(To see info from two different tables with same data definition, duplication only) With INTERSECT If the tables are seen as ensembles, then only the intersection between the tables is listed
Eg:
SELECT stor_id, title_id, qty FROM sales_america
INTERSECT
SELECT stor_id, title_id, qty FROM sales_europe
(To see info from two different tables with same data definition, duplication extracted) With MINUS If the tables are seen as ensembles, then only the data from the first table is listed less the common data and the data from the second table
Eg:
SELECT stor_id, title_id, qty FROM sales_america
MINUS
SELECT stor_id, title_id, qty FROM sales_europe
INSERT
Eg:
INSERT INTO products(prod_id, description)
VALUES(34,’pants’)
Eg:
INSERT INTO highprice(prod_id,prod_code,price)
SELECT prod_id, prod_code, price –(have to have same columns)
FROM products
WHERE price > 20.00
UPDATE
Eg:
UPDATE stocks
SET qty = 0
WHERE warehouse_id = 10
DELETE
Eg:
DELETE FROM warehouse
WHERE location = ‘Chicago’
ORDER BY
Eg:
SELECT *
FROM products
WHERE prod_code = ‘H’
ORDER BY price DESC –ASC is the default
Comparison, range, patterns, IN, BETWEEN operators
Comparison operators:
= equal to
<> not equal to
> greater than
< less than
>= greater than or equal
<= less than or equal
Range operators: BETWEEN:
Eg:
SELECT product_id
FROM products
WHERE price BETWEEN 3 AND 20
Set membership operator: IN
Eg:
SELECT product_id
FROM products
WHERE product_code IN (‘H’,’E’)
Pattern matching operator: LIKE
Eg:
SELECT product_id, description
FROM products
WHERE description LIKE ‘Pipe%’ OR
WHERE description LIKE ‘%Pipe%’ OR
WHERE description LIKE ‘Pipe 2_mm%’
% wildcard for several characters (percent sign)
_ wildcard for one character(underscore sign)
Logical Operators: IS NULL
Eg:
SELECT product_id, price
FROM products
WHERE price IS NULL
–null is not equal to zero
BETWEEN, IN, LIKE, IS NULL can all be negated by the NOT operator.
NOT BETWEEN
NOT IN
NOT LIKE
IS NOT NULL
Arithmetic Expressions:
Arithmetic Expressions: + – * /
Used to generate virtual/temporary column in query results
Eg:
SELECT description, price, (price*1.05)new_price
FROM products
–note: name of new column must follow the parentheses
Logical Connectives:
Logical Connectives: AND OR
Eg:
SELECT product_id, price
FROM products
WHERE price>10 OR product_id=10
–note: In a query the AND is satisfied first
using IN( , , ) has the same result as using OR
Aggregate Functions:
SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.
AVG () gives the average of the given column.
MAX () gives the largest figure in the given column.
MIN () gives the smallest figure in the given column.
COUNT(*) gives the number of rows satisfying the conditions.
Note : Takes entire column of data and produces a single data item that summarizes the column. They are: AVG( ) SUM( ) COUNT( ) MAX( ) MIN( )
Eg:
SELECT COUNT(*)
FROM stocks –COUNT(*) will count nulls
Eg:
SELECT COUNT(DISTINCT region)
FROM warehouses –Use of word DISTINCT eliminate duplicate in count
Eg:
SELECT COUNT(Qty)
FROM stocks –Does not count nulls
Eg:
SELECT COUNT(*), MAX(price), MIN(price), AVG(price)
FROM products
GROUP BY
Eg:
SELECT prod_code, AVG(price)
FROM products
GROUP BY prod_code
— note: the column to GROUP BY has to be in the SELECT column
HAVING (WHERE clause of the GROUP BY)
— The GROUP BY clause (with aggregate fctn):
Eg:
SELECT state, COUNT(*) AS ‘Total’
FROM authors
GROUP BY state
— The HAVING clause (with aggregate fctn):
Eg:
SELECT state, COUNT(*) AS ‘Total’
FROM authors
GROUP BY state
HAVING COUNT(*) > 1
JOIN
Example of an equi-join (When there is an exact match between the columns)
Eg:
SELECT products.prod_id, description –(full path name avoids ambiguity if
FROM products, stocks — column name is similar in two tables)
WHERE products.prod_id= stocks.prod_id –(joint statement)
AND qty = 5
An example of a natural join follows.
It produces a product of every combination of the two tables!!! Not used frequently since it returns useless information
Eg:
SELECT *
FROM products, stocks
An example of a join on more than two tables follows:
Eg:
SELECT description, qty, location
FROM products, stocks, warehouses
WHERE products.prod_id= stocks.prod_id
AND stocks.warehouse_id = warehouses.warehouse_id
AND qty < 5
ALIAS
Eg:
SELECT description, qty, location
FROM products p, stocks s, warehouses w — <- alias declaration
WHERE p.prod_id= s.prod_id
AND s.warehouse_id = w.warehouse_id
AND qty < 5
Self-join are used in a multi-table query involving a relationship a table has with itself.
Eg:
SELECT right.location
FROM warehouses left, warehouses right
WHERE left.region= right.region
AND left.warehouse_id <> right.warehouse_id
An outer-join is needed when a value in a joining column in one table has no matching value in the joined table.
Eg:
SELECT location, region_name
FROM region r
FULL OUTER JOIN warehouses w
ON r.region_id = w.region_id
Subquery
Eg:
SELECT prod_id, description
FROM products
WHERE product_id IN(SELECT prod_id
FROM stocks
WHERE qty > 50)
Inner Join statements (rows must be present in each tables that match on the join condition):
Eg:
SELECT p.pub_id, pub_name, title
FROM publishers AS p INNER JOIN titles AS t
ON
p.pub_id = t.pub_id
WHERE type = ‘business’
Eg:
SELECT stor_name, title, ord_date, qty
FROM stores
INNER JOIN sales ON stores.stor_id = sales.stor_id
INNER JOIN titles ON sales.title_id = titles.tles_id
WHERE type = ‘popular_comp’
Left and right outer join
(Left joins return rows with left table column info that have matching condition but null info on column from the right side table) :
Eg:
SELECT titles.title_id, title, ord_date, qty
FROM titles LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id
VIEW
Eg:
CREATE VIEW James_view AS
SELECT prod_id, description
FROM products
WHERE price < 17.50;
DDL (Data Definition Language)
CREATE also using UNIQUE, NOT NULL, ASSERTION, DOMAIN, CHECK…
ALTER used to add a column, add/delete primary/foreign keys, add/drop a uniqueness or
Check constraint
DROP When using RESTRICT Drop will fail if table has objects dependencies
CREATE VIEW
CREATE INDEX
The catalog holds information about roles and privileges. It has information on VIEWS.
Eg:
DROP TABLE customer
Eg: To create a table:
CREATE TABLE title
(
Au_id ID,
Title_id TID,
au_ord TINYINT NULL,
typer INT NULL
)
Eg: To create a temporary table (visible to present connection, will be deleted when logout)
CREATE TABLE #temp1
(
Au_id ID,
Title_id TID
)
Eg: To create a global temporary table (visible to all connections on server, will be deleted when all logout)
CREATE TABLE ##temp1 (Au_id ID, Title_id TID)
Eg: To create a view
CREATE VIEW myfirstview
AS SELECT fname, lname, address
FROM customers
Eg: To remove a view from the database
DROP VIEW myfirstview
Eg: To change a view
ALTER VIEW myfirstview (store_name, qty_sold, date_sold, title)
AS SELECT store, qty, date, title_name
FROM stores INNER JOIN sales
ON sales.stor_id = stores.stor_id
DCL (Data Control Language)
GRANT priviledge TO username
IDENTIFIED BY password;
CREATE SYNONYM Used to shorten user/owner of a table.
CREATE PUBLIC SYNONYM … FOR … ; Public synonyms can be created by the DBA.
COMMIT and ROLLBACK
An explict transaction is a group of statements that must all succeed or must all fail. to turn ON the ANSI SQL-92 behaviour use:
SET IMPLICIT_TRANSACTION ON
–ANSI SQL-92
COMMIT WORK
ROLLBACK WORK
COMMIT; –makes changes made to some database systems permanent (since the last COMMIT; known as a transaction)
ROLLBACK; –Takes back any changes to the database that you have made, back to the last time you gave a Commit command…beware! Some software uses automatic committing on systems that use the transaction features, so the Rollback command may not work.
Mathematical Functions:
ABS(X) Absolute value-converts negative numbers to positive, or leaves positive numbers alone
CEIL(X) X is a decimal value that will be rounded up.
FLOOR(X) X is a decimal value that will be rounded down.
GREATEST(X,Y) Returns the largest of the two values.
LEAST(X,Y) Returns the smallest of the two values.
MOD(X,Y) Returns the remainder of X / Y.
POWER(X,Y) Returns X to the power of Y.
ROUND(X,Y) Rounds X to Y decimal places. If Y is omitted, X is rounded to the nearest integer.
SIGN(X) Returns a minus if X < 0, else a plus.
SQRT(X) Returns the square root of X.
Character Functions
LEFT(<string>,X) Returns the leftmost X characters of the string.
RIGHT(<string>,X) Returns the rightmost X characters of the string.
UPPER(<string>) Converts the string to all uppercase letters.
LOWER(<string>) Converts the string to all lowercase letters.
INITCAP(<string>) Converts the string to initial caps.
LENGTH(<string>) Returns the number of characters in the string.
<string>||<string> Combines the two strings of text into one, concatenated string, where the first string is immediately followed by the second
LPAD(<string>,X,’*’) Pads the string on the left with the * (or whatever character is inside the quotes), to make the string X characters long.
RPAD(<string>,X,’*’) Pads the string on the right with the * (or whatever character is inside the quotes), to make the string X characters long.
SUBSTR(<string>,X,Y) Extracts Y letters from the string beginning at position X.
NVL(<column>,<value>)
The Null value function will substitute <value> for any NULLs for in the <column>. If the current value of <column> is not NULL, NVL has no effect.
| Command | Meaning |
| / | Executes the SQL buffer |
| ? [Keyword] | Provides SQL help on the keyword |
| @[@] [Filename] [Parameter list] | Runs the specified command file, passing the specified parameters |
| ACC[EPT] Variable [DEF[AULT] Value] [PROMPT Text | NOPR[OMPT]] | Allows the user to enter the value of a substitution variable |
| CL[EAR] [SCR[EEN]] | Clears the screen |
| CL[EAR] SQL | Clears the SQL buffer |
| COL[UMN] [Column] [Format] | Defines the format of a column, displays the format of a column, or displays all column formats |
| CON[NECT] [username/password@database] | Connects to the database with the speciffied user |
| DEF[INE] [Variable] [ = Text] | Defines a substitution variable, displays a variable, or displays all substitution variables. |
| DESC[RIBE] Object | Gives a description of the specified object |
| DISC[CONNECT] | Disconnects from the database |
| EDIT | Displays a text editor to edit the SQL buffer |
| EXEC[UTE] Procedure | Executes the specified procedure |
| EXIT | Quits a running script or closes the Command Window |
| GET [Filename] | Loads a command file into the editor |
| HOST [Command] | Executes the host command |
| HELP [Keyword] | Provides SQL help on the keyword |
| INFO | Displays information about the connection |
| PAUSE [Message] | Displays the message and pauses until the user presses return |
| PRI[NT] [Variable] | Displays the value of the bind variable, or all bind variables |
| PROMPT [Text] | Displays the specified text |
| QUIT | Quits a running script or closes the Command Window |
| R[UN] | Executes the SQL buffer |
| REM[ARK] [Text] | A comment line |
| SET AUTOP[RINT] [ON | OFF] | Determines if bind variables are automatically displayed after executing a SQL statement or PL/SQL block. |
| SET CON[CAT] [Character | ON | OFF] | Determines the character that terminates a substitution variable reference (default = .) |
| SET DEF[INE] [Character | ON | OFF] | Determines the character that starts a substitution variable reference (default = &) |
| SET ECHO [ON | OFF] | Determines if executed commands in a script are displayed |
| SET ESC[APE] [Character | ON | OFF] | Determines the character that escapes the character that starts a substitution variable reference (default = \) |
| SET FEED[BACK] [ON | OFF] | Determines if the number of affected rows of a SQL statement is displayed |
| SET HEA[DING] [ON | OFF] | Determines if headings are displayed above the columns of a result set |
| SET LONG [Width] | Determines the maximum display width of a long column |
| SET PAGES[IZE] [Size] | Determines the number of lines that are displayed for a result set, before the headings are repeated |
| SET SERVEROUT[PUT] [ON | OFF] [SIZE n] | Determines if output of calls to dbms_output.put_line is displayed, and what the size of the output buffer is |
| SET TERM[OUT] [ON | OFF] | Determines if output of executed SQL statements is displayed |
| SET TIMI[NG] [ON | OFF] | Determines if timing information about executed SQL statements is displayed |
| SET VER[IFY] [ON | OFF] | Determines if substitution variables are displayed when used in a SQL statement or PL/SQL block |
| SHO[W] REL[EASE] | Displays Oracle release information for the current connection |
| SHO[W] SQLCODE | Displays the result code of the executed SQL statement |
| SHO[W] USER | Displays the username of the current connection |
| SPO[OL] [Filename | OFF] | Starts or stops spooling |
| STA[RT] [Filename] [Parameter list] | Runs the specified command file, passing the specified parameters |
| STORE SET [Filename] | Stores the values of all options in the filename. You can execute this file later to restore these options. |
| UNDEF[INE] Variable | Undefines the given substitution variable |
| VAR[IABLE] [Variable] [Datatype] | Defines a bind variable, displays a bind variable, or displays all bind variables. |
Most asked SQL queries in Software Testing Interview
Display the dept information from department table.
select * from dept;
Display the details of all employees.
select * from emp;
Display the name and job for all employees.
select ename, job from emp;
Display name and salary for all employees.
select ename, sal from emp;
Display employee number and total salary for each employee.
select empno, sal+comm from emp;
Display employee name and annual salary for all employees.
select empno, empname, 12*sal+nvl(comm,0) annualsal from emp;
Display the names of all employees who are working in department number 10.
select ename from emp where deptno=10;
Display the names of all employees working as clerks and drawing a salary more than 3000.
select ename from emp where job=’CLERK’ and sal>3000;
Display employee number and names for employees who earn commission.
select empno, ename from emp where comm is not null and comm>0;
Display names of employees who do not earn any commission.
Select empno, ename from emp where comm is null and comm=0;
Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 3000.
select ename from emp where (job=’CLERK’ or job=’SALESMAN’ or job=’ANALYST’) and sal>3000;
(or)
select ename from emp where job in(‘CLERK’,’SALESMAN’,’ANALYST’) `
and sal>3000;
Display the names of employees who are working in the company for the past 5 years.
select ename from emp where sysdate-hiredate>5*365;
Display the list of employees who have joined the company before 30th June 90 or after 31st dec 90.
select * from emp where hiredate between ‘30-jun-1990’ and ‘31-dec-1990’;
Display current date.
select sysdate from dual;
Display the list of users in your database (using log table).
select * from dba_users;
Display the names of all tables from the current user.
select * from tab;
Display the name of the current user.
show user;
Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst.
select ename from emp where deptno in (10,20,40) or job in (‘CLERK’,’SALESMAN’,’ANALYST’);
Display the names of employees whose name starts with alphabet S.
select ename from emp where ename like ‘S%’;
Display employee names for employees whose name ends with alphabet.
select ename from emp where ename like ‘%S’;
Display the names of employees whose names have second alphabet S in their names.
select ename from emp where ename like ‘_S%’;
Display the names of employees whose name is exactly five characters in length.
select ename from emp where length(ename)=5;
(or)
select ename from emp where ename like ‘_____’;
Display the names of employees who are not working as managers.
select * from emp minus (select * from emp where empno in (select mgr from emp));
(or)
select * from emp where empno not in (select mgr from emp where mgr is not null);
(or)
select * from emp e where empno not in (select mgr from emp where e.empno=mgr)
Display the names of employees who are not working as SALESMAN or CLERK or ANALYST.
select ename from emp where job not in (‘CLERK’,’SALESMAN’,’ANALYST’);
Display all rows from EMP table. The system should wait after every screen full of information.
set pause on;
Display the total number of employees working in the company.
select count(*) from emp;
Display the total salary being paid to all employees.
select sum(sal)+sum(nvl(comm,0)) from emp;
Display the maximum salary from emp table.
select max(sal) from emp;
Display the minimum salary from emp table.
select min(sal) from emp;
Display the average salary from emp table.
select avg(sal) from emp;
Display the maximum salary being paid to CLERK.
select max(sal) from emp where job=’CLERK’;
Display the maximum salary being paid in dept no 20.
select max(sal) from emp where deptno=20;
Display the min Sal being paid to any SALESMAN.
select min(sal) from emp where job=’SALESMAN’;
Display the average salary drawn by managers.
select avg(sal) from emp where job=’MANAGER’;
Display the total salary drawn by analyst working in dept no 40.
select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;
Display the names of employees in order of salary i.e. the name of the employee earning lowest salary should appear first.
select ename from emp order by sal;
Display the names of employees in descending order of salary.
select ename from emp order by sal desc;
Display the details from emp table in order of emp name.
select ename from emp order by ename;
Display empno, ename, deptno, and sal. Sort the output first based on name and within name by deptno and within deptno by Sal;
select * from emp order by ename,deptno,sal;
Display the name of the employee along with their annual salary (Sal * 12). The name of the employee earning highest annual salary should appear first.
select ename, 12*(sal+nvl(comm,0)) Annual from emp order by 12*(sal+nvl(comm,0)) desc;
Display name, Sal, hra, pf, da, total Sal for each employee. The output should be in the order of total Sal, hra 15% of Sal, da 10% of sal, pf 5% of sal total salary will be (sal*hra*da)-pf.
select ename,sal,sal*15/100 HRA, sal*5/100 PF, sal*10/100 DA,sal+sal*15/100-sal*5/100+sal*10/100 TOTAL_SALARY from emp
Display dept numbers and total number of employees within each group.
select deptno,count(*) from emp group by deptno;
Display the various jobs and total number of employees with each job group.
select job, count(*) from emp group by job;
Display department numbers and total salary for each department.
select deptno, sum(sal) from emp group by deptno;
Display department numbers and maximum salary for each department.
select deptno, max(sal),min(sal) from emp group by deptno;
Display the various jobs and total salary for each job.
select job, sum(sal) from emp group by job;
Display each job along with minimum sal being paid in each job group.
select job, min(sal) from emp group by job;
Display the department numbers with more than three employees in each dept.
select deptno, count(*) from emp group by deptno having count(*)>3;
Display the various jobs along with total sal for each of the jobs where total sal is greater than 40000.
select job, sum(sal) from emp group by job having sum(sal)>40000;
Display the various jobs along with total number of employees in each job. The output should contain only those jobs with more than three employees.
select job, count(*) from emp group by job having count(*)>3;
Display the name of emp who earns highest sal.
select ename from emp where sal=(select max(sal) from emp);
Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS.
select empno, ename from emp where job=’CLERK’ and sal=(select max(sal) from emp where job=’CLERK’);
Display the names of the salesman who earns a salary more than the highest salary of any clerk.
select ename from emp where job=’SALESMAN’ and sal >
(select max(sal) from emp where job=’CLERK’);
Display the names of clerks who earn salary more than that of James of that of sal lesser than that of Scott.
select ename from emp where job=’CLERK’ and sal<(select sal from emp where ename=’SCOTT’) and sal>(select sal from emp where ename=’JAMES’);
Display the names of employees who earn a Sal more than that of James or that of salary greater than that of Scott.
select ename from emp where sal <
(select sal from emp where ename=’SCOTT’) and sal >
(select sal from emp where ename=’JAMES’);
Display the names of the employees who earn highest salary in their respective departments.
select * from emp e where sal =
(select max(sal) from emp where deptno=e.deptno)
Display the names of employees who earn highest salaries in their respective job groups.
select * from emp e where sal in
(select max(sal) from emp group by job having e.job=job)
Display the employee names who are working in accountings dept.
select ename from emp where deptno =
(select deptno from dept where dname=”ACCOUNTING”);
(or)
select ename from emp where deptno in (select deptno from dept where dname=”ACCOUNTING”);
Display the employee names who are working in Chicago.
select ename from emp where deptno =
(select deptno from dept where loc=’CHICAGO’);
Display the job groups having total salary greater then the maximum salary for managers.
select job, sum(sal) from emp group by job having sum(sal) >
(select max(sal) from emp where job=’MANAGER’);
Display the names of employees from department number 10 with salary greater than that of any employee working in other departments.
select ename,sal,deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno!=deptno);
Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.
select ename, sal, deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno != deptno);
Display the names of employees in Upper case.
select upper(ename) from emp;
Display the names of employees in lower case.
select lower(ename) from emp;
Display the name of employees in proper case.
select initcap(ename) from emp;
Find out the length of your name using appropriate function.
select length(‘India’) from dual;
Display the length of all employees’ names.
select sum(length(ename)) from emp;
Display the name of the employee concatenate with EMP no.
select ename||empno from emp;
(or)
select concat(ename,empno) from emp;
Use appropriate function and extract 3 characters starting from 2 characters from the following string ‘Oracle’ i.e. the output should be ‘rac’.
select substr(‘oracle’,2,3) from dual;
Find the first occurrence of character a from the following string ‘computer maintenance corporation’.
select instr(‘computer maintenance corporation’,’a’,1,1) from dual;
Replace every occurrence of alphabet A with B in the string Allen’s (user translate function).
select replace(‘Allens’,’A’,’b’) from dual;
Display the information from EMP table. Wherever job ‘manager’ is found it should be displayed as boss(replace function).
select empno, ename, replace(job, ‘MANAGER’, ‘Boss’) JOB from emp;
Display empno, ename, deptno from EMP table. Instead of display department numbers display the related department name (use decode function).
select e.empno, e.ename, d.dname from emp e,dept d where
e.deptno = d.deptno;
Display your age in days.
select round(sysdate-to_date(’15-aug-1947′)) from dual;
Display your age in months.
select floor(months_between(sysdate,’15-aug-1947′))
“age in months” from dual;
Display current date as 15th august Friday nineteen forty seven.
select to_char(sysdate,’ddth month day year’) from dual;
Display the following output for each row from EMP table as ‘scott has joined the company on Wednesday 13th august nineteen ninety’.
select ename||’ has joined the company on ‘||to_char(hiredate,’day ddth month year’) from emp;
Find the date of nearest Saturday after current day.
select next_day(sysdate, ‘SATURDAY’) from dual;
Display current time.
select to_char(sysdate,’hh:mi:ss’) Time from dual;
Display the date three months before the current date.
select add_months(sysdate,-3) from dual;
Display the common jobs from department number 10 and 20.
select job from emp where deptno=10 and job in(select job from emp where deptno=20);
(or)
select job from emp where deptno=10 intersect select job from emp where deptno=20;
Display the jobs found in department number 10 and 20 eliminate duplicate jobs.
select distinct(job) from emp where deptno=10 and job in(select job from emp where deptno=20);
(or)
select job from emp where deptno=10 intersect select job from emp where deptno=20;
Display the jobs which are unique to dept no 10.
select job from emp where deptno=10 minus select job from emp where deptno!=10;
(or)
select job from emp where deptno = 10 and job not in (select job from emp where deptno<>10);
Display the details of those who do not have any person working under them.
select empno from emp where empno not in (select mgr from emp where mgr is not null);
Display the details of employees who are in sales dept and grade is 3.
select * from emp where sal>=(select losal from salgrade where grade=3) and sal<=(select hisal from salgrade where grade=3) and deptno=(select deptno from dept where dname=’SALES’);
Display those who are not managers and who are managers any one.
select * from emp where empno in(select mgr from emp) union
select * from emp where empno not in(select mgr from emp where mgr is not null);
Display those employees whose name contains not less than 4 chars.
Select * from emp where length(ename)>4;
Display those departments whose name start with ‘S’ while location name end with ‘O’.
select * from dept where dname like ‘S%’ and loc like ‘%O’;
Display those employees whose manager name is JONES.
select * from emp where mgr=(select empno from emp where ename=’JONES’);
Display those employees whose salary is more than 3000 after giving 20% increment.
select * from emp where sal*120/100 > 3000;
(or)
select * from emp where sal+sal*20/100 > 3000;
Display all employees with there dept name.
select ename, dname from emp e, dept d where e.deptno = d.deptno;
Display ename who are working in sales dept.
select empno, ename from emp where
deptno=(select deptno from dept where dname=’SALES’);
Display employee name, deptname, salary and comm. for those Sal in between 2000 to 5000 while location is Chicago.
select empno,ename,deptno from emp where deptno=(select deptno from dept where loc=’CHICAGO’) and sal between 2000 and 5000;
Display those employees whose salary greater than his manager salary.
select * from emp e where sal>(select sal from emp where empno=e.mgr);
Display those employees who are working in the same dept where his manager is working.
select * from emp e where
deptno = (select deptno from emp where empno=e.mgr);
Display those employees who are not working under any manger.
select * from emp where mgr is null or empno=mgr;
Display grade and employees name for the dept no 10 or 30 but grade is not 4, while joined the company before 31-dec-82.
select empno,ename,sal,deptno,hiredate,grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal and deptno in(10,30) and grade<>4 and hiredate<’01-dec-1981′;
Update the salary of each employee by 10% increments that are not eligible for commission.
update emp set sal=sal+(sal*10/100) where comm is null;
Delete those employees who joined the company before 31-dec-82 while there dept location is ‘NEW YORK’ or ‘CHICAGO’.
delete from emp where hiredate<’31-dec-1982′ and deptno in
(select deptno from dept where loc in(‘NEW YORK’,’CHICAGO’));
Display employee name, job, deptname, location for all who are working as managers.
select ename,job,dname,loc from emp e, dept d where e.deptno=d.deptno and empno in (select mgr from emp);
Display those employees whose manager names is Jones, and also display there manager name.
select e.empno, e.ename, m.ename MANAGER from emp e, emp m
where e.mgr=m.empno and m.ename=’JONES’;
Display name and salary of ford if his Sal is equal to high Sal of his grade.
select ename,sal from emp e where ename=’FORD’ and sal=(select hisal from salgrade where grade=(select grade from salgrade where e.sal>=losal and e.sal<=hisal));
Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise.
break on deptno;
select d.deptno, e.ename, e.job, d.dname, m.ename, s.grade from
emp e, emp m, dept d, salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno order by e.deptno;
List out all the employees name, job, and salary grade and department name for every one in the company except ‘CLERK’. Sort on salary display the highest salary.
select empno, ename, sal, dname, grade from emp e, dept d, salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.job<>’CLERK’ order by sal;
Display employee name, his job and his manager. Display also employees who are without manager.
select e.ename, e.job, m.ename Manager from emp e,emp m where e.mgr=m.empno union select ename,job,’no manager’ from emp where mgr is null;
Find out the top 5 earner of company.
select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;
Display the name of those employees who are getting highest salary.
select empno,ename,sal from emp where sal=(select max(sal) from emp);
Display those employees whose salary is equal to average of maximum and minimum.
select * from emp where sal=(select (max(sal)+min(sal))/2 from emp);
Display count of employees in each department where count greater than 3.
select deptno, count(*) from emp group by deptno having count(*)>3;
Display dname where at least 3 are working and display only dname.
select dname from dept where deptno in
(select deptno from emp group by deptno having count(*)>3);
Display name of those managers name whose salary is more than average salary of company.
select ename, sal from emp where empno in(select mgr from emp) and sal > (select avg(sal) from emp);
Display those managers name whose salary is more than an average salary of his employees.
select ename, sal from emp e where empno in(select mgr from emp) and e.sal>(select avg(sal) from emp where mgr=e.empno);
Display employee name, Sal, comm and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?
select ename, sal, comm, sal+nvl(comm,0) netPay from emp where sal+nvl(comm.,0)>=any(select sal from emp);
Display those employees whose salary is less than his manager but more than salary of any other managers.
select * from emp e where sal<(select sal from emp where empno = e.mgr) and sal>any(select sal from emp where empno!=e.mgr);
Display all employees names with total Sal of company with employee name.
Select ename,
Find out the last 5(least) earner of the company?
select * from emp e where 5>(select count(*) from emp where sal<e.sal) order by sal;
Find out the number of employees whose salary is greater than there manager salary?
select count(*) from emp e where sal>(select sal from emp where empno=e.mgr);
Display those manager who are not working under president but they are working under any other manager?
select * from emp e where mgr in(select empno from emp where ename<>’KING’);
Delete those department where no employee working?
delete from dept d where 0=(select count(*) from emp where deptno=d.deptno);
Delete those records from EMP table whose deptno not available in dept table?
delete from emp where deptno not in(select deptno from dept);
Display those earners whose salary is out of the grade available in Sal grade table?
select * from emp where sal<(select min(losal) from salgrade) or sal>(select max(hisal) from salgrade);
Display employee name, Sal, comm. and whose net pay is greater than any other in the company?
Select ename, sal, comm from emp where sal+sal*15/100-sal*5/100 +sal*10/100 = (select max(sal+sal*15/100-sal*5/100+sal*10/100) from emp);
Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?
select * from emp where (to_date(’31-dec-1999′)-hiredate)/365>18;
Display those employees whose salary is ODD value?
select * from emp where mod(sal,2)=1;
Display those employees whose salary contains at least 4 digits?
select * from emp where length(sal)>=4;
Display those employees who joined in the company in the month of DEC?
select * from emp where upper(to_char(hiredate,’mon’))=’DEC’;
Display those employees whose name contains “A”?
select * from emp where instr(ename,’A’,1,1)>0;
Display those employees whose deptno is available in salary?
select * from emp where instr(sal,deptno,1,1)>0;
Display those employees whose first 2 characters from hire date-last 2 characters of salary?
select substr(hiredate,0,2)||substr(sal,length(sal)-1,2) from emp;
select concat( substr(hiredate,0,2), substr(sal,length(sal)-1,2) ) from emp;
Display those employees whose 10% of salary is equal to the year of joining?
select * from emp where to_char(hiredate,’yy’)=sal*10/100;
Display those employees who are working in sales or research?
select * from emp where deptno in(select deptno from dept where dname in(‘SALES’,’RESEARCH’));
Display the grade of Jones?
select grade from salgrade where losal<=(select(sal) from emp where ename=’JONES’) and hisal>=(select(sal) from emp where ename=’JONES’);
Display those employees who joined the company before 15th of the month?
select empno,ename from emp where hiredate<(to_date(’15-‘||to_char(hiredate,’mon’)||’-‘||to_char(hiredate,’yyyy’)));
Delete those records where no of employee in a particular department is less than 3?
delete from emp where deptno in(select deptno from emp group by deptno having count(*)>3);
Delete those employees who joined the company 21 years back from today?
select * from emp where round((sysdate-hiredate)/365)>21; or
select * from emp where (to_char (sysdate, ‘yyyy’)-to_char (hiredate ,’yyyy’) )>21;
Display the department name the no of characters of which is equal to no of employees in any other department?
Select dname from dept where length(dname) in (select count(*) from emp group by deptno);
Display those employees who are working as manager?
select * from emp where empno in(select mgr from emp);
Count the no of employees who are working as manager (use set operation)?
select count(*) from emp where empno in(select mgr from emp);
Display the name of then dept those employees who joined the company on the same date?
select empno,ename,hiredate,deptno from emp e where hiredate in (select hiredate from emp where empno<>e.empno);
Display those employees whose grade is equal to any number of Sal but not equal to first number of Sal?
Display the manager who is having maximum number of employees working under him?
Select mgr from emp group by mgr having count(*)=(select max(count(mgr)) from emp group by mgr);
List out employees name and salary increased by 15% and expressed as whole number of dollars?
select empno,ename,lpad(concat(‘$’,round(sal*115/100)),7) salary from emp;
Produce the output of the EMP table “EMPLOYEE_AND_JOB” for ename and job?
select * from EMPLOYEE_AND_JOB;
List all employees with hire date in the format ‘June 4 1988’?
select to_char(hiredate,’month dd yyyy’) from emp;
Print a list of employees displaying ‘Less Salary’ if less than 1500 if exactly 1500 display as ‘Exact Salary’ and if greater than 1500 display ‘More Salary’?
select empno,ename,’Less Salary ‘||sal from emp where sal<1500
union
select empno,ename,’More Salary ‘||sal from emp where sal>1500
union
select empno,ename,’Exact Salary ‘||sal from emp where sal=1500
Write query to calculate the length of employee has been with the company?
Select round(sysdate-hiredate) from emp;
Given a String of the format ‘nn/nn’ verify that the first and last 2 characters are numbers. And that the middle characters is ‘y’ print the expressions ‘Yes’ if valid ‘No’ of not valid use the following values to test your solution
Employees hire on 15th of any month are paid on the last Friday of that month. Those hired after 15th are paid the last Friday of the following month. print a list of employees their hire date and first pay date sort those whose Sal contains first digits of their dept.
Display those mangers who are getting less than his employees Sal.
Select empno from emp e where sal<any(select sal from emp where mgr=e.empno);
Print the details of all the employees who are sub ordinate to Blake.
Select * from emp where mgr=(select empno from emp where ename=’BLAKE’);
Display those who working as manager using co related sub query.
Select * from emp where empno in(select mgr from emp);
Display those employees whose manger name is Jones and also with his manager name.
Select * from emp where mgr=(select empno from emp where ename=’JONES’) union select * from emp where empno=(select mgr from emp where ename=’JONES’);
Define variable representing the expressions used to calculate on employee’s total annual renumaration.
define emp_ann_sal=(sal+nvl(comm,0))*12;
Use the variable in a statement which finds all employees who can earn 30,000 a year or more.
select * from emp where &emp_ann_sal>30000;
Find out how many mangers are there with out listing them.
Select count (*) from EMP where empno in (select mgr from EMP);
Find out the avg sal and avg total remuneration for each job type remember salesman earn commission.
select job,avg(sal+nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by job;
Check whether all employees number are indeed unique.
select count(empno),count(distinct(empno)) from emp having count(empno)=(count(distinct(empno)));
List out the lowest paid employees working for each manager, exclude any groups where min sal is less than 1000 sort the output by sal.
select e.ename,e.mgr,e.sal from emp e where sal in(select min(sal) from emp where mgr=e.mgr) and e.sal>1000 order by sal;
list ename, job, annual sal, deptno, dname and grade who earn 30000 per year and who are not clerks.
Select e.ename, e.job, (e.sal+nvl(e.comm,0))*12, e.deptno, d.dname, s.grade from emp e, salgrade s , dept d where e.sal between s.losal and s.hisal and e.deptno=d.deptno and (e.sal+nvl(comm,0))*12> 30000 and e.job <> ‘CLERK’;
find out the job that was failed in the first half of 1983 and the same job that was failed during the same period on 1984.
find out the all employees who joined the company before their manager.
Select * from emp e where hiredate<(select hiredate from emp where empno=e.mgr);
list out the all employees by name and number along with their manager’s name and number also display ‘No Manager’ who has no manager.
select e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where e.mgr=m.empno
union
select empno,ename,mgr,’No Manager’ from emp where mgr is null;
find out the employees who earned the highest Sal in each job typed sort in descending Sal order.
select * from emp e where sal =(select max(sal) from emp where job=e.job);
find out the employees who earned the min Sal for their job in ascending order.
select * from emp e where sal =(select min(sal) from emp where job=e.job) order by sal;
find out the most recently hired employees in each dept order by hire date
select * from emp order by deptno,hiredate desc;
display ename, sal and deptno for each employee who earn a Sal greater than the avg of their department order by deptno
select ename,sal,deptno from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) order by deptno;
display the department where there are no employees
select deptno,dname from dept where deptno not in(select distinct(deptno) from emp);
display the dept no with highest annual remuneration bill as compensation.
select deptno,sum(sal) from emp group by deptno having sum(sal) = (select max(sum(sal)) from emp group by deptno);
In which year did most people join the company. Display the year and number of employees
select count(*),to_char(hiredate,’yyyy’) from emp group by to_char(hiredate,’yyyy’);
display avg sal figure for the dept
select deptno,avg(sal) from emp group by deptno;
Write a query of display against the row of the most recently hired employee. display ename hire date and column max date showing.
select empno,hiredate from emp where hiredate=(select max (hiredate) from emp);
display employees who can earn more than lowest Sal in dept no 30
select * from emp where sal>(select min(sal) from emp where deptno=30);
find employees who can earn more than every employees in dept no 30
select * from emp where sal>(select max(sal) from emp where deptno=30);
select * from emp where sal>all(select sal from emp where deptno=30);
select dept name dept no and sum of Sal
break on deptno on dname;
select e.deptno,d.dname,sal from emp e, dept d where e.deptno=d.deptno order by e.deptno;
find out avg sal and avg total remainders for each job type
find all dept’s which have more than 3 employees
select deptno from emp group by deptno having count(*)>3;
If the pay day is next Friday after 15th and 30th of every month. What is the next pay day from their hire date for employee in emp table
If an employee is taken by you today in your organization. And is a policy in your company to have a review after 9 months the joined date (and of 1st of next month after 9 months) how many days from today your employee has to wait for a review
Display employee name and his sal whose sal is greater than highest avg of dept no
Display the 10th record of EMP table. (without using rowid)
Display the half of the enames in upper case and remaining lower case
select concat ( upper ( substr ( ename, 0 , length (ename)/ 2) ),
lower (substr (ename, length(ename) / 2+1, length(ename) )) ) from emp;
display the 10th record of emp table without using group by and rowed
Delete the 10th record of emp table.
Create a copy of emp table.
Create table emp1 as select * from emp;
Select ename if ename exists more than once.
select distinct(ename) from emp e where ename in(select ename from emp where e.empno<>empno);
display all enames in reverse order.
select ename from emp order by ename desc;
Display those employee whose joining of month and grade is equal.
select empno,ename from emp e, salgrade s where e.sal between s.losal and s.hisal and to_char(hiredate,’mm’)=grade;
Display those employee whose joining date is available in dept no
select * from emp where to_char(hiredate,’dd’)=deptno;
Display those employees name as follows A ALLEN, B BLAKE
select substr(ename,1,1)||’ ‘||ename from emp;
List out the employees ename, sal, PF from emp
Select ename,sal,sal*15/100 PF from emp;
Display RSPS from emp without using updating, inserting
Create table emp with only one column empno
Create table emp (empno number(5));
Add this column to emp table ename Varchar(20).
alter table emp add ename varchar2(20) not null;
OOPS! I forgot to give the primary key constraint. Add it now.
alter table emp add constraint emp_empno primary key (empno);
Now increase the length of ename column to 30 characters.
alter table emp modify ename varchar2(30);
Add salary column to emp table.
alter table emp add sal number(7,2);
I want to give a validation saying that sal cannot be greater 10,000(note give a name to this column).
alter table emp add constraint emp_sal_check check (sal<10000);
For the time being I have decided that I will not impose this validation. My boss has agreed to pay more than 10,000.
Alter table emp disable constraint emp_sal_check;
My boss has changed his mind. Now he doesn’t want to pay more than 10,000. So revoke that salary constraint
Alter table emp enable constraint emp_sal_check;
Add column called as mgr to your emp table.
Alter table emp add mgr number(5);
Oh! This column should be related to empno. Give a command to add this constraint
Alter table emp add constraint emp_mgr foreign key(empno);
Add dept no column to your emp table
Alter table emp add deptno number(3);
This dept no column should be related to deptno column of dept table
Alter table emp1 add constraint emp1_deptno foreign key(deptno) references dept(deptno);
Create table called as new emp. Using single command create this table as well as to get data into this table (use create table as)
create table newemp as select *from emp;
Create table called as newemp. This table should contain only empno,ename, dname
create table newemp as select empno,ename,dname from emp e , dept d where e.deptno=d.deptno;
Delete the rows of employees who are working in the company for more than 2 years.
Delete from emp where floor(sysdate-hiredate)>2*365;
Provide a commission to employees who are not earning any commission.
update emp set comm=300 where comm is null;
If any employee has commission his commission should be incremented by 10% of his salary.
update emp set comm=comm*10/100 where comm is not null;
Display employee name and department name for each employee.
select ename,dname from emp e, dept d where e.deptno=d.deptno;
Display employee number, name and location of the department in which he is working.
Select empno, ename, loc from emp e, dept d where e.deptno=d.deptno;
Display ename, dname even if there no employees working in a particular department(use outer join).
Select ename, dname from emp e, dept d where e.deptno (+)= d.deptno;
Display employee name and his manager name.
Select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
Display the department name along with total salary in each department.
Select deptno, sum(sal) from emp group by deptno;
Display the department name and total number of employees in each department.
select deptno,count(*) from emp group by deptno;
Alter table emp1 add constraint emp1_deptno foreign key(deptno) references dept(deptno)
Delete from emp where job name is clerk
Insert into emp without giving any further commands move to another client system and log into the same user give the following command
Are the above changes reflected in this user
Go to your fist system and give commit come back to the second system and give the following command
Display the current date and time
select to_char(sysdate,’month mon dd yy yyyy hh:mi:ss’) from dual;
RakeshHansalia's Blog 