0% found this document useful (0 votes)
31 views4 pages

SQL Module 1 Assignment Tasks

1. The document provides the problem statement and tasks for an SQL assignment involving three tables: STUDIES, SOFTWARE, and PROGRAMMER. 2. The tasks include finding average selling cost by language, displaying names and ages of programmers, and displaying details of software, programmers, and packages that meet certain criteria. 3. Sample CREATE TABLE statements are provided to define the three tables, along with INSERT statements to populate them with data.

Uploaded by

sandip naik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
31 views4 pages

SQL Module 1 Assignment Tasks

1. The document provides the problem statement and tasks for an SQL assignment involving three tables: STUDIES, SOFTWARE, and PROGRAMMER. 2. The tasks include finding average selling cost by language, displaying names and ages of programmers, and displaying details of software, programmers, and packages that meet certain criteria. 3. Sample CREATE TABLE statements are provided to define the three tables, along with INSERT statements to populate them with data.

Uploaded by

sandip naik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL Module 1 Assignment

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?

CREATE TABLE
studies(PNAMEvarchar(20),INSTITUTEvarchar(20),COURSEvarchar(20),COURSE_FEEint)G
O
SELECT*FROM studies
INSERT INTO studies
SELECT'ANAND','SABHARI','PGDCA',4500UNIONALL
SELECT'ALTAF','COIT','DCA',7200UNIONALL
SELECT'JULIANA','BDPS','MCA',22000UNIONALL
SELECT'KAMALA','PRAGATHI','DCA',5000UNIONALL
SELECT'MARY','SABHARI','PGDCA ',4500UNIONALL
SELECT'NELSON','PRAGATHI','DAP',6200UNIONALL
SELECT'PATRICK','PRAGATHI','DCAP',5200UNIONALL
SELECT'QADIR','APPLE','HDCA',14000UNIONALL
SELECT'RAMESH','SABHARI','PGDCA',4500UNIONALL
SELECT'REBECCA','BRILLIANT','DCAP',11000UNIONALL
SELECT'REMITHA','BDPS','DCS',6000UNIONALL
SELECT'REVATHI','SABHARI','DAP',5000UNIONALL
SELECT'VIJAYA','BDPS','DCA',48000
GO
RESULT
SELECT*FROM Studies
ANAND SABHARI PGDCA 4500
ALTAF COIT DCA 7200
JULIANA BDPS MCA 22000
KAMALA PRAGATHI DCA 5000
MARYSABHARI PGDCA 4500
NELSON PRAGATHI DAP 6200
PATRICKPRAGATHI DCAP 5200
QADIR APPLEH DCA 14000
RAMESH SABHARI PGDCA 4500
REBECCA BRILLIANT DCAP 11000
REMITHA BDPS DCS 6000
REVATHI SABHARI DAP 5000
VIJAYA BDPS DCA 4800

CREATE TABLE
software(PNAMEvarchar(20),TITLEvarchar(20),DEVELOPINvarchar(20),SCOSTdecimal(10,2),DCOSTint,S
OLDint)GOSELECT*FROMsoftware

INSERT INTOsoftware
SELECT'MARY','README','CPP',300,1200,84UNIONALL
SELECT'ANAND','PARACHUTES','BASIC',399.95,6000,43UNIONALL
SELECT'ANAND','VIDEO TITLING','PASCAL',7500,16000,9UNIONALL
SELECT'JULIANA','INVENTORY','COBOL',3000,3500,0UNIONALL
SELECT'KAMALA','PAYROLL PKG.','DBASE',9000,20000,7UNIONALL
SELECT'MARY','FINANCIAL ACCT.','ORACLE',18000,85000,4UNIONALLSELECT'MARY','CODE
GENERATOR','C',4500,20000,23UNIONALL
SELECT'PATTRICK','README','CPP',300,1200,84UNIONALL
SELECT'QADIR','BOMBS AWAY','ASSEMBLY',750,3000,11UNIONALL
SELECT'QADIR','VACCINES','C',1900,3100,21UNIONALL
SELECT'RAMESH','HOTEL MGMT.','DBASE',13000,35000,4UNIONALL
SELECT'RAMESH','DEAD LEE','PASCAL',599.95,4500,73UNIONALL
SELECT'REMITHA','PC UTILITIES','C',725,5000,51UNIONALL
SELECT'REMITHA','TSR HELP PKG.','ASSEMBLY',2500,6000,7UNIONALL
SELECT'REVATHI','HOSPITAL MGMT.','PASCAL',1100,75000,2UNIONALL
SELECT'VIJAYA','TSR EDITOR','C',900,700,6
Go

RESULT
SELECT*FROMSoftware
PNAME TITLEDEVELOPIN SCOST DCOST SOLD
MARY README CPP 300.00 1200 84
ANAND PARACHUTE SBASIC 399.95 6000 43
ANAND VIDEO TITLING PASCAL 7500.00 1600 09
JULIAN AINVENTORY COBOL 3000.00 350 00
KAMALA PAYROLL PKG. DBASE 9000.00200 007
MARY FINANCIAL [Link] 18000.0 08500 04
MARY CODE GENERATORC 4500.00 20000 23
PATTRICK README CPP 300.00 1200 84
QADIRBOMBS AWAYASSEMBLY 750.00 3000 11
QADIR VACCINESC 1900.00 3100 21
RAMESH HOTEL [Link] 13000.00 35000 4
RAMESH DEAD LEEPASCAL 599.95 4500 73
REMITHA PC UTILITIESC 725.00 5000 51
REMITHATSR HELP [Link] 2500.006000 7
REVATHIHOSPITAL [Link] 1100.007500 02
VIJAYA TSR EDITORC 900.00 700 6
CREATE
TABLEPROGRAMMER(PNAMEVARCHAR(20),DOBDATE,DOJDATE,GENDERVARCHAR(1),PROF1VAR
CHAR(15),PROF2VARCHAR(15),SALARYINT)INSERT INTOprogrammer
SELECT'ANAND','12-Apr-66','21-Apr-92','M','PASCAL','BASIC',3200UNIONALL
SELECT'ALTAF','02-Jul-64','13-Nov-90','M','CLIPPER','COBOL',2800UNIONALL
SELECT'JULIANA','31-Jan-60','21-Apr-90','F','COBOL','DBASE',3000UNIONALL
SELECT'KAMALA','30-Oct-68','02-Jan-92','F','C','DBASE',2900UNIONALL
SELECT'MARY','24-Jun-70','01-Feb-91','F','CPP','ORACLE',4500UNIONALL
SELECT'NELSON','11-Sep-85','11-Oct-89','M','COBOL','DBASE',2500UNIONALL
SELECT'PATTRICK','10-Nov-65','21-Apr-90','M','PASCAL','CLIPPER',2800UNIONALL
SELECT'QADIR','31-Aug-65','21-Apr-91','M','ASSEMBLY

Common questions

Powered by AI

Examining the joining dates against professional achievements and salaries shows how early joining dates, like that of Juliana who joined in April 1990, correlate with considerable skill engagement such as COBOL and DBASE proficiency. However, her salary (3000) is not the highest, suggesting other factors like market trends or project involvement contribute to salary outcomes, unlike Mary who joined later and commands 4500. Thus, while earlier joining provides experience, crucial is the alignment with evolving technology demands, influencing project involvement and consequential financial gains.

The dataset reveals interesting insights into gender distribution and course selection. Among the listed programmers, a noticeable majority select practical courses such as PGDCA, DAP, and MCA, with varying course fee structures. The choice of courses appears not directly segregated by gender but rather institution-based, such as those by Sabhari and BDPS. There is potential indication of gender parity in course enrollments, with prevalent selections reflecting demands of certain professional skills across both genders, evidenced by a shared enrollment in diverse courses such as DAP by both Nelson and Revathi.

Classic languages like COBOL and assembly still manifest practical relevancy in modern contexts. In the dataset, Juliana and Qadir, proficient in COBOL and assembly respectively, leverage these skills in project deployments like 'INVENTORY' and 'BOMBS AWAY.' Despite their lower profile in contemporary software markets relative to modern languages, such proficiencies provide niche advantages, filling key technical gaps in specific legacy system updates and operations, suggesting enduring industry significance and specialization advantage in select sectors.

Linking course fees to the software outputs by the graduates can infer how educational investment impacts development calibre. For instance, Juliana, an MCA graduate from BDPS with a course fee of 22000, has developed 'INVENTORY,' priced at 3000, while Mary, with an ORACLE and CPP background, generates high-value solutions, e.g., 'FINANCIAL ACCT.' at 18000, surpassing educational investment. This disparity suggests that while course expense doesn't linearly predict economic output of developed software, it influences technical aptitude required for creating competitively valued software.

The correlation between programming language proficiency and professional roles can be quantified by examining the 'programmer' table. For instance, Mary, proficient in CPP and ORACLE, commands a higher salary of 4500, compared to others like Pattrick with Pascal and Clipper proficiency at 2800. This suggests that mastery of in-demand languages like CPP and database management systems like ORACLE may result in higher remunerations. Thus, languages considered modern and market-relevant likely impact career progression and salary prospects positively.

The evaluation of courses like DAP requires cross-referencing the 'studies' and 'programmer' tables. From the data, programmers who completed the DAP course are Revathi and Nelson. Observing their professional information, both are male and have skills in COBOL and DBASE (Nelson), and programming in PASCAL (Revathi). Although on face value, the course may not directly relate to their professed skills, completion of DAP might broaden career pathways or competencies, exemplified by Revathi's contribution to 'HOSPITAL MGMT.' developed in Pascal.

The average selling cost of packages developed in Pascal is calculated by taking the average of the selling cost 'SCOST' for all entries where the 'DEVELOPIN' field is 'PASCAL'. From the data, the relevant packages are 'VIDEO TITLING' by ANAND with a cost of 7500.00, 'DEAD LEE' by RAMESH with a cost of 599.95, and 'HOSPITAL MGMT.' by REVATHI with a cost of 1100.00. The average is (7500.00 + 599.95 + 1100.00) / 3 = 3066.65.

To analyze the relationships between programmers and the software they developed, join operations can be used on the 'programmer' and 'software' tables using the 'PNAME' field, which is common in both tables. This produces a combined dataset in which the software each programmer has worked on can be detailed, along with their respective programming skills, salaries, and other relevant information from both tables. For instance, this allows us to identify that RAMESH developed 'HOTEL MGMT.' and 'DEAD LEE'.

An effective strategy for improving package development involves analyzing the cost-to-recovery ratio for packages. From the dataset, packages such as 'DEAD LEE' and 'VIDEO TITLING,' which have recovered costs but not surpassed at a ratio greater than double, indicate a need for optimizing development and sales strategies. Implementing cost control measures during development, enhancing package features to increase marketability, and strategic pricing adjustments could improve financial outcomes. Leveraging programmers proficient in languages that allow cost-effective development could also be key.

From the dataset, male programmers predominantly have studied languages such as Clipper, COBOL, and Pascal. For example, Anand who studied Pascal further developed 'VIDEO TITLING.' Meanwhile, Nelson who knew COBOL contributed differently, not linked directly in the data. The insight indicates that while languages studied, such as Pascal, do find application in real project deliveries, career flexibility often requires adapting to varying demands beyond initial studies, illustrating a broad-learning approach is beneficial for adaptability and practical application.

You might also like