Easy SQL

                        SQL Assignment – Module 3  SQL Assignment – Module 3 Questions : 1. How many Programmers Don’t know PASCAL and C  2. Display the details of those who don’t know Clipper, COBOL or PASCAL.  3. Display each language name with AVG Development Cost, AVG Selling Cost and AVG Price per Copy.  4. List the programmer names (from the programmer table) and No. Of Packages each has developed.  5. List each PROFIT with the number of Programmers having that PROF and the number of the packages in that PROF.  6. How many packages are developed by the most experienced programmer form BDPS.  7. How many packages were developed by the female programmers earning more than the highest paid male programmer?  8. How much does the person who developed the highest selling package earn and what course did HE/SHE undergo.  9. In which institute did the person who developed the costliest...

                        SQL Assignment – Module 2  SQL Assignment – Module 2 Questions : 1) What is the Highest Number of copies sold by a Package?  2) Display lowest course Fee.   3) How old is the Oldest Male Programmer.  4) What is the AVG age of Female Programmers?  5) Calculate the Experience in Years for each Programmer and Display with their names in Descending order.  6) How many programmers have done the PGDCA Course?  7) How much revenue has been earned thru sales of Packages Developed in C.  8) How many Programmers Studied at Sabhari?  9) How many Packages Developed in DBASE?  10) How many programmers studied in Pragathi?  11) How many Programmers Paid 5000 to 10000 for their course?  12) How many Programmers know either COBOL or PASCAL?  13) How many Female Programmers are there?  14) What is the AVG Salary?  15) How many people draw sal...

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

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

 Q.  What is the difference between count(0), count(1), and count(*) in MySQL/SQL? Ans.  The difference in “count(<whatever>)” is mostly if you have count(<fixed value>) versus count(<expression>)   select count(<fixed value>) - as long as it isn’t a literal  NULL  - ends up being the same  as count(*) .  And no, 0 isn’t special; select count(0) is the same as select count(1). select count(<expression>)  is actually interesting. It adds 1 for each row where <expression> evaluates to non- NULL , and 0 otherwise. So, you can do the following: select count(somecol) from sometab;   returns the total count of records where somecol IS NOT NULL select count(if(somecol > 50, 1, NULL)) from sometab;   returns the total count of records where somecol is > 50 The above may seem silly, until you realize that you can use count() multiple times, ie select count(if(somecol > 50, 1, NULL)),   ...

Q.  What is the SQL query to find the count of records in a table that have a NULL value in a column? Ans.  If you want to find this in any specific column, it’s pretty straightforward… select count(*) from sometab where somecol IS NULL; or select count(somecol) from sometab; In the latter, each record that has a somecol that is non-NULL will add one to the count, or zero otherwise. If you want to find a count of records where _any_ column is NULL, unfortunately there aren’t any ways that avoid just listing the columns and OR IS NULL stuff (or something logically similar), ie select count(*) from sometab where col1 IS NULL or col2 IS NULL or ... or colN IS NULL;