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', '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


--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

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 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 ...
Read more