Easy SQL

Python Basics Assignment #Q1. n = int(input('put the value the of n :')) r = float(input('put the value the of r :')) p = float(input('put the value the of p :')) print('value of n : ',n) print('value of r : ',r) print('value of p : ',p) A = p * (1 + r / 100) ** n print('value of A = ',A) #Q2. A = int(input('put the value the of A :')) B = int(input('put the value the of A :')) print('value of n : ',A) print('value of r : ',B) Str = f"There are {A} students in the class, with {B} who play at least one sport." print(Str) ##or## print(f"There are {A} students in the class, with {B} who play at least one sport.") #Q3. sample= "It goes without saying, \"Time is Money\", and none can deny it." print(sample) #Q4. import math x = lambda a,b: a//b x(10,3) #Q5. a = int(input('input the value of a : ')) b = int(input('input the value of b : ')) print(...

                                                 SQL Assignment – Module 5  SQL Assignment – Module 5 Questions : 1. Display the names of the highest paid programmers for each Language.  2. Display the details of those who are drawing the same salary.  3. Who are the programmers who joined on the same day?  4. Who are the programmers who have the same Prof2?  5. How many packages were developed by the person who developed the cheapest package, where did he/she study? Answer  : ---ARNAB BOSE--- SELECT * FROM PROGRAMMER SELECT * FROM SOFTWARE SELECT * FROM STUDIES ---Q1--- WITH LanguageHighestPaid AS (     SELECT DISTINCT         PROF1 AS LANGUAGE,         FIRST_VALUE(PNAME) OVER(PARTITION BY PROF1 ORDER BY SALARY DESC) AS HIGHEST_PAID_PROGRAMMER     FROM PR...

                                                 SQL Assignment – NEW   SQL Assignment – NEW Questions : 1 Display the “FIRST_NAME” from Employee table using the alias name as Employee_name.  2 Display “LAST_NAME” from Employee table in upper case.  3 Display unique values of DEPARTMENT from EMPLOYEE table.  4 Display the first three characters of LAST_NAME from EMPLOYEE table.  5 Display the unique values of DEPARTMENT from EMPLOYEE table and prints its length.  6 Display the FIRST_NAME and LAST_NAME from EMPLOYEE table into a single column AS FULL_NAME. a space char should separate them.  7 DISPLAY all EMPLOYEE details from the employee table order by FIRST_NAME Ascending.  8. Display all EMPLOYEE details order by FIRST_NAME Ascending and DEPARTMENT Descending.  9 Display details for EMPLOYEE...

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

                        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;