SQL Assignment – Module 1

SQL Assignment – Module 1 

                      SQL Assignment – Module 1 SQL Assignment – Module 1

Questions

Problem Statement: Consider yourself to be Sam and you have been given the below tasks to complete using the Table – STUDIES, SOFTWARE & PROGRAMMER.

 1. Find out the selling cost AVG for packages developed in Pascal. 

2. Display Names, Ages of all Programmers.

 3. Display the Names of those who have done the DAP Course. 

4. Display the Names and Date of Births of all Programmers Born in January.

 5. Display the Details of the Software Developed by Ramesh.

 6. Display the Details of Packages for which Development Cost have been recovered.

 7. Display the details of the Programmers Knowing C.

 8. What are the Languages studied by Male Programmers? 9

. Display the details of the Programmers who joined before 1990. 

10. Who are the authors of the Packages, which have recovered more than double the Development cost?

 


ALL Answers:

----- ARNAB BOSE -----

 

 CREATE TABLE STUDIES (

    PNAME VARCHAR(500),

    INSTITUTE VARCHAR(500),

    COURSE VARCHAR(500),

    --COURSE_FEE decimal (10,2)

----------or

COURSE_FEE int

);



INSERT INTO STUDIES (PNAME, INSTITUTE, COURSE, COURSE_FEE)

VALUES

    ('ANAND', 'SABHARI', 'PGDCA', 4500),

    ('ALTAF', 'COIT', 'DCA', 7200),

    ('JULIANA', 'BDPS', 'MCA', 22000),

    ('KAMALA', 'PRAGATHI', 'DCA', 5000),

    ('MARY', 'SABHARI', 'PGDCA', 4500),

    ('NELSON', 'PRAGATHI', 'DAP', 6200),

    ('PATRICK', 'PRAGATHI', 'DCAP', 5200),

    ('QADIR', 'APPLE', 'HDCA', 14000),

    ('RAMESH', 'SABHARI', 'PGDCA', 4500),

    ('REBECCA', 'BRILLIANT', 'DCAP', 11000),

    ('REMITHA', 'BDPS', 'DCS', 6000),

    ('REVATHI', 'SABHARI', 'DAP', 5000),

    ('VIJAYA', 'BDPS', 'DCA', 48000);

Select * FROM STUDIES

--Drop table STUDIES--


CREATE TABLE SOFTWARE (

    PNAME VARCHAR(150),

    TITLE VARCHAR(150),

    DEVELOPIN VARCHAR(150),

    SCOST DECIMAL(10, 2),

    DCOST DECIMAL(10, 2),

    SOLD INT

);



INSERT INTO SOFTWARE (PNAME, TITLE, DEVELOPIN, SCOST, DCOST, SOLD)

VALUES

    ('MARY', 'README', 'CPP', 300, 1200, 84),

    ('ANAND', 'PARACHUTES', 'BASIC', 399.95, 6000, 43),

    ('ANAND', 'VIDEO TITLING', 'PASCAL', 7500, 16000, 9),

    ('JULIANA', 'INVENTORY', 'COBOL', 3000, 3500, 0),

    ('KAMALA', 'PAYROLL PKG.', 'DBASE', 9000, 20000, 7),

    ('MARY', 'FINANCIAL ACCT.', 'ORACLE', 18000, 85000, 4),

    ('MARY', 'CODE GENERATOR', 'C', 4500, 20000, 23),

    ('PATTRICK', 'README', 'CPP', 300, 1200, 84),

    ('QADIR', 'BOMBS AWAY', 'ASSEMBLY', 750, 3000, 11),

    ('QADIR', 'VACCINES', 'C', 1900, 3100, 21),

    ('RAMESH', 'HOTEL MGMT.', 'DBASE', 13000, 35000, 4),

    ('RAMESH', 'DEAD LEE', 'PASCAL', 599.95, 4500, 73),

    ('REMITHA', 'PC UTILITIES', 'C', 725, 5000, 51),

    ('REMITHA', 'TSR HELP PKG.', 'ASSEMBLY', 2500, 6000, 7),

    ('REVATHI', 'HOSPITAL MGMT.', 'PASCAL', 1100, 75000, 2),

    ('VIJAYA', 'TSR EDITOR', 'C', 900, 700, 6);


Select * FROM SOFTWARE

--Drop table SOFTWARE --


CREATE TABLE PROGRAMMER (

    PNAME VARCHAR(150),

    DOB DATE ,

    DOJ DATE,

    GENDER CHAR(5),

    PROF1 VARCHAR(150),

    PROF2 VARCHAR(150),

    SALARY DECIMAL(10, 2)

);




--INSERT INTO PROGRAMMER (PNAME, DOB, DOJ, GENDER, PROF1, PROF2, SALARY)

--VALUES

    ('ANAND', '1966-04-12', '1992-04-21', 'M', 'PASCAL', 'BASIC', 3200),

    ('ALTAF', '1964-07-02', '1990-11-13', 'M', 'CLIPPER', 'COBOL', 2800),

    ('JULIANA', '1960-01-31', '1990-04-21', 'F', 'COBOL', 'DBASE', 3000),

    ('KAMALA', '1968-10-30', '1992-01-02', 'F', 'C', 'DBASE', 2900),

    ('MARY', '1970-06-24', '1991-02-01', 'F', 'CPP', 'ORACLE', 4500),

    ('NELSON', '1985-09-11', '1989-10-11', 'M', 'COBOL', 'DBASE', 2500),

    ('PATTRICK', '1965-11-10', '1990-04-21', 'M', 'PASCAL', 'CLIPPER', 2800),

    ('QADIR', '1965-08-31', '1991-04-21', 'M', 'ASSEMBLY', 'C', 3000),

    ('RAMESH', '1967-05-03', '1991-02-28', 'M', 'PASCAL', 'DBASE', 3200),

    ('REBECCA', '1967-01-01', '1990-12-01', 'F', 'BASIC', 'COBOL', 2500),

    ('REMITHA', '1970-04-19', '1993-04-20', 'F', 'C', 'ASSEMBLY', 3600),

    ('REVATHI', '1969-12-02', '1992-01-02', 'F', 'PASCAL', 'BASIC', 3700),

    ('VIJAYA', '1965-12-14', '1992-05-02', 'F', 'FOXPRO', 'C', 3500);


--SELECT * FROM PROGRAMMER

--- I does't convert '1990-12-14' to '1990-Dec-14' into statement Create an insert table I want to convert ('1990-12-14' to '1990-Dec-14') '1990-12-14' in Insert statement and output of the table '1990-Dec-14' ,

-not this way ->

--SELECT PNAME, CONVERT(VARCHAR(20), DOB, 106) AS DOB, CONVERT(VARCHAR(20), DOJ, 106) AS DOJ, GENDER, PROF1, PROF2, SALARY FROM PROGRAMMER;

--- DROP TABLE PROGRAMMER---


CREATE TABLE PROGRAMMER (

    PNAME VARCHAR(150),

    DOB VARCHAR(20),

    DOJ VARCHAR(20),

    GENDER CHAR(5),

    PROF1 VARCHAR(150),

    PROF2 VARCHAR(150),

    SALARY DECIMAL(10, 2)

);


INSERT INTO PROGRAMMER (PNAME, DOB, DOJ, GENDER, PROF1, PROF2, SALARY)

VALUES

    ('ANAND', '12-Apr-66', '21-Apr-92', 'M', 'PASCAL', 'BASIC', 3200),

    ('ALTAF', '02-Jul-64', '13-Nov-90', 'M', 'CLIPPER', 'COBOL', 2800),

    ('JULIANA', '31-Jan-60', '21-Apr-90', 'F', 'COBOL', 'DBASE', 3000),

    ('KAMALA', '30-Oct-68', '02-Jan-92', 'F', 'C', 'DBASE', 2900),

    ('MARY', '24-Jun-70', '01-Feb-91', 'F', 'CPP', 'ORACLE', 4500),

    ('NELSON', '11-Sep-85', '11-Oct-89', 'M', 'COBOL', 'DBASE', 2500),

    ('PATTRICK', '10-Nov-65', '21-Apr-90', 'M', 'PASCAL', 'CLIPPER', 2800),

    ('QADIR', '31-Aug-65', '21-Apr-91', 'M', 'ASSEMBLY', 'C', 3000),

    ('RAMESH', '03-May-67', '28-Feb-91', 'M', 'PASCAL', 'DBASE', 3200),

    ('REBECCA', '01-Jan-67', '01-Dec-90', 'F', 'BASIC', 'COBOL', 2500),

    ('REMITHA', '19-Apr-70', '20-Apr-93', 'F', 'C', 'ASSEMBLY', 3600),

    ('REVATHI', '02-Dec-69', '02-Jan-92', 'F', 'PASCAL', 'BASIC', 3700),

    ('VIJAYA', '14-Dec-65', '02-May-92', 'F', 'FOXPRO', 'C', 3500);


SELECT * FROM PROGRAMMER


 SELECT * FROM PROGRAMMER

 SELECT * FROM SOFTWARE

 SELECT * FROM STUDIES

 --DROP TABLE PROGRAMMER

 --DROP TABLE SOFTWARE

 --DROP TABLE STUDIES

 ------ CODES------

 

 

 --Q1


 SELECT AVG(SCOST) AVG_SCOST

 FROM SOFTWARE

 WHERE DEVELOPIN='PASCAL'

--AVG OF ALL--

 --SELECT AVG(SCOST) AVG_SCOST FROM SOFTWARE


 --Q2--

  SELECT PROGRAMMER.PNAME NAME , 

 DATEDIFF(YEAR,DOB,GETDATE()) AGE_YEARS

 FROM PROGRAMMER

 -- AGE DIFF OF DAY,MONTH,YEAR --

 SELECT PROGRAMMER.PNAME NAME , 

 DATEDIFF(YEAR,DOB,GETDATE()) AGE_YEARS,

 DATEDIFF(MONTH,DOB,GETDATE()) AGE_MONTHS,

 DATEDIFF(DAY,DOB,GETDATE()) AGE_DAYS

 FROM PROGRAMMER


 ---ARNAB BOSE---

 --Q3--

 

 SELECT STUDIES.PNAME NAME,

 STUDIES.INSTITUTE,

 STUDIES.COURSE,

 STUDIES.COURSE_FEE

 FROM STUDIES

 WHERE COURSE='DAP'


 --Q4--


 SELECT PROGRAMMER.PNAME NAME,

 PROGRAMMER.DOB

 FROM PROGRAMMER

 WHERE MONTH(DOB)=01


  --Q5--


   SELECT * FROM SOFTWARE

   WHERE SOFTWARE.PNAME='RAMESH'


   --Q6--


 SELECT * FROM SOFTWARE

 WHERE SOFTWARE.DCOST>SOFTWARE.SCOST

 --- I consider if Dcost more than Scost then it is recovered I do not considered the equal if you considered then (SELECT * FROM SOFTWARE WHERE SOFTWARE.DCOST>=SOFTWARE.SCOST)---


  --Q7--

   SELECT *

FROM PROGRAMMER

WHERE PROF1 = 'C' OR PROF2 = 'C'

 

 --OR--


  SELECT * FROM SOFTWARE

  WHERE SOFTWARE.DEVELOPIN='C'



   --Q8--


SELECT DISTINCT PROF1 [Codes Known By Mens]

FROM PROGRAMMER

WHERE GENDER = 'M'

UNION

SELECT DISTINCT PROF2

FROM PROGRAMMER

WHERE GENDER = 'M'


 

 --Q9--


 SELECT * FROM  PROGRAMMER

 WHERE YEAR(DOJ) < 1990


 

 ---Q10--


 SELECT PNAME NAME

FROM SOFTWARE

WHERE DCOST * 2 < SOLD * SCOST



----ARNAB BOSE----









Comments

Post a Comment

Popular posts from this blog

SQL Assignment – Module 4

                          SQL Assignment – Module 4  SQL Assignment – Module 4 Questions : 1. What is the cost of the costliest software development in Basic?  2. Display details of Packages whose sales crossed the 2000 Mark.  3. Who are the Programmers who celebrate their Birthdays during the Current Month?  4. Display the Cost of Package Developed By each Programmer.  5. Display the sales values of the Packages Developed by each Programmer.  6. Display the Number of Packages sold by Each Programmer.  7. Display each programmer’s name, costliest and cheapest Packages Developed by him or her.  8. Display each institute name with the number of Courses, Average Cost per Course.  9. Display each institute Name with Number of Students.  10. List the programmers (form the software table) and the institutes they studied.  11. How many packages were developed by students, who...
Read more

SQL Assignments TABLE

Assignments TABLE CREATE TABLE STUDIES (     PNAME VARCHAR(500),     INSTITUTE VARCHAR(500),     COURSE VARCHAR(500),     --COURSE_FEE decimal (10,2) ----------or COURSE_FEE int ); INSERT INTO STUDIES (PNAME, INSTITUTE, COURSE, COURSE_FEE) VALUES     ('ANAND', 'SABHARI', 'PGDCA', 4500),     ('ALTAF', 'COIT', 'DCA', 7200),     ('JULIANA', 'BDPS', 'MCA', 22000),     ('KAMALA', 'PRAGATHI', 'DCA', 5000),     ('MARY', 'SABHARI', 'PGDCA', 4500),     ('NELSON', 'PRAGATHI', 'DAP', 6200),     ('PATRICK', 'PRAGATHI', 'DCAP', 5200),     ('QADIR', 'APPLE', 'HDCA', 14000),     ('RAMESH', 'SABHARI', 'PGDCA', 4500),     ('REBECCA', 'BRILLIANT', 'DCAP', 11000),     ('REMITHA', 'BDPS', 'DCS', 6000),     ('REVATHI', 'SABHARI...
Read more