- Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)
- What operator performs pattern matching?
LIKE operator
- What operator tests column for the absence of data?
IS NULL operator
- Which command executes the contents of a specified file?
START <filename> or @<filename>
- What is the parameter substitution symbol used with INSERT INTO command?
&
- Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
- What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution
- State true or false. EXISTS, SOME, ANY are operators in SQL.
True
- State true or false. !=, <>, ^= all denote the same operation.
True
- 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
- What command is used to get back the privileges offered by the GRANT command?
REVOKE
- Which system tables contain information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
- Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.
- 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.
- 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.
- What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;
TROUBLETHETROUBLE
- 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.
- 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.
- Which date function is used to find the difference between two dates?
MONTHS_BETWEEN
- 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.
- 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.
- What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
- 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
- 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.
- 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.
- Which function is used to find the largest integer less than or equal to a specific value?
FLOOR
- 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
- List all the employees who have at least one person reporting to them.
- List the employee details if and only if more than 10 employees are present in department no 10.
- List the name of the employees with their immediate higher authority.
- List all the employees who do not manage any one.
- List the employee details whose salary is greater than the lowest salary of an employee belonging to deptno 20.
- List the details of the employee earning more than the highest paid manager.
- List the highest salary paid for each job.
- Find the most recently hired employee in each department.
- In which year did most people join the company? Display the year and the number of employees.
- Which department has the highest annual remuneration bill?
- Write a query to display a ‘*’ against the row of the most recently hired employee.
- Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
- Find the nth maximum salary.
- Select the duplicate records (Records, which are inserted, that already exist) in the EMP table.
- 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;