UDN-企业互联网技术人气社区

板块导航

浏览  : 908
回复  : 0

[面试相关] 关于SQL的一套笔试题 附带答案

[复制链接]
顶风尿三丈的头像 楼主
发表于 2015-6-1 11:47:21 | 显示全部楼层 |阅读模式
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
    [/ol]
    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);
    [/ol]
    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

    [ol]
  • 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).
    [/ol]

    KEYS:

    [ol]
  • 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
  • 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;
    [/ol]
  • 您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    关于我们
    联系我们
    • 电话:010-86393388
    • 邮件:udn@yonyou.com
    • 地址:北京市海淀区北清路68号
    移动客户端下载
    关注我们
    • 微信公众号:yonyouudn
    • 扫描右侧二维码关注我们
    • 专注企业互联网的技术社区
    版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
    快速回复 返回列表 返回顶部