हिंदी

Differentiate between count() and count(*) functions in SQL with appropriate examples. - Computer Science (Python)

Advertisements
Advertisements

प्रश्न

Differentiate between count() and count(*) functions in SQL with appropriate examples.

संक्षेप में उत्तर
अंतर स्पष्ट करें

उत्तर

COUNT(*) returns the count of all rows in the table, whereas COUNT() is used with Column_Name passed as an argument and counts the number of non-NULL values in a column that is given as an argument.

Example: Table: EMPL

EMPNO ENAME JOB SAL DEPTNO
8270 JACK SALESMAN 2985 10
8566 ALI CLERK 9870 20
8922 AJAY NULL 8760 30
8736 BINDU CLERK 5643 20
8822 JOY MANAGER 3000 10

e.g. SELECT COUNT(*) FROM EMPL;

Output

COUNT(*)
5

e.g. SELECT COUNT(JOB) FROM EMPL;

Output

COUNT(JOB)
4

Since JOB has 1 NULL value.

shaalaa.com
Functions in SQL
  क्या इस प्रश्न या उत्तर में कोई त्रुटि है?
2022-2023 (March) Sample

संबंधित प्रश्न

______ is used to get a specified day of the month for a given date.


______ keyword is used to find out the number of values in a column?


The MAX () function finds the


Consider the table STUDENT given below:

RollNo Name Class DOB Gender City Marks
1 Anand XI 6/6/97 M Agra 430
2 Chetan XII 7/5/94 M Mumbai 460
3 Geet XI 6/5/97 F Agra 470
4 Preeti XII 8/8/95 F Mumbai 492
5 Saniyal XII 8/10/95 M Delhi 360
6 Maakhiy XI 12/12/94 F Dubai 256
7 Neha X 8/12/95 F Moscow 324
8 Nishant X 12/6/95 M Moscow 429

State the command that will give the output as:

Name
Anand
Chetan
Geet
Preeti

(i) select Name from student where Class ='XI' and Class='XII';

(ii) select name from student where not Class='XI' and Class='XII';

(iii) select name from student where City="Agra" or City="Mumbai";

(iv) select name from student where City in("Agra", "Mumbai");

Choose the correct option:


Consider the table STUDENT given below:

RollNo Name Class DOB Gender City Marks
1 Anand XI 6/6/97 M Agra 430
2 Chetan XII 7/5/94 M Mumbai 460
3 Geet XI 6/5/97 F Agra 470
4 Preeti XII 8/8/95 F Mumbai 492
5 Saniyal XII 8/10/95 M Delhi 360
6 Maakhiy XI 12/12/94 F Dubai 256
7 Neha X 8/12/95 F Moscow 324
8 Nishant X 12/6/95 M Moscow 429

State the command to display the average marks scored by students of each gender who are in class XI?

(i) Select Gender, avg(Marks) from STUDENT where Class= "XI" group by Gender;

(ii) Select Gender, avg(Marks) from STUDENT group by Gender where Class="XI";

(iii) Select Gender, avg(Marks) group by Gender from STUDENT having Class="XI";

(iv) Select Gender, avg(Marks) from STUDENT group by Gender having Class = "XI";

Choose the correct option:


Assertion (A): COUNf function ignores DISTINCT

Reason (R): DISTINCT ignores the duplicate values.


Which of the following is not an aggregate function?


The char() function in MySql is an example of ______. 


Which command is used to delete a table? (in SQL)


Identify Single Row function of MySQL among the following.


What will be returned by the given query? Select round(23456.1234, -2) = ______.


What will be returned by the given query? select mid("Computer", 2, 4) = ______.


What will be returned by the given query? select day of year('2015-02-10') = ______.


What will be returned by the given query? Select length(trim("ABC Public School")) = ______.


What will be returned by the given query? SELECT RIGHT("LEFT", 2) = ______.


What will be returned by the given query? SELECT MID('LeaminglsFun', 2, 4) = ______.


"COUNT" keyword belongs to which categories in Mysql?


What will be returned by the given query? SELECT INSTR('INFORMATIONFORM', 'FOR') = ______.


Which of the following is not a date function?


Which function will be used to remove only the trailing spaces from a string?


The string function that returns the index of the first occurrence of substring is ______.


What is the general format of UPPER() return type?


Which statement is used to count the number of rows in table?


Site any two differences between Single Row Functions and Aggregate Functions.


Write the name of the functions to perform the following operation:

To display the day like “Monday”, and “Tuesday”, from the date when India got independence.


Write the name of the functions to perform the following operation:

To display the name of the month in which you were born.


Write the name of the functions to perform the following operation:

To display your name in capital letters.


Write the output produced by the following SQL statement:

SELECT ROUND(342.9234, -1);


Write the output produced by the following SQL statement:

SELECT    YEAR(“1979/11/26”),
MONTH(“1979/11/26”),    DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);


Write the outputs of the SQL queries based on the relations Teacher and Placement given below:

Table: Teacher

T_ID Name Age Department Date_of_join Salary Gender
1 Arunan 34 Computer Sc 2019-01-10 12000 M
2 Saman 31 History 2017-03-24 20000 F
3 Randeep 32 Mathematics 2020-12-12 30000 M
4 Samira 35 History 2018-07-01 40000 F
5 Raman 42 Mathematics 2021-09-05 25000 M
6 Shyam 50 History 2019-06-27 30000 M
7 Shiv 44 Computer Sc 2019-02-25 21000 M
8 Shalakha 33 Mathematics 2018-07-31 20000 F

Table: Placement

P_ID Department Place
1 History Ahmedabad
2 Mathematics Jaipur
3 Computer Sc Nagpur
  1. SELECT Department, avg(salary) FROM Teacher GROUP BY Department;
  2. SELECT MAX(Date_of_Join), MIN(Date_of_Join) FROM Teacher;
  3. SELECT Name, Salary, T.Department, Place FROM Teacher T, Placement P WHERE T.Department = P.Department AND Salary>20000;
  4. SELECT Name, Place FROM Teacher T, Placement P WHERE Gender = ’F’ AND T.Department = P.Department;

Predict the output of the code given below:

s="welcome2cs" 

n = len(s)
m=""
for i in range(0, n):
       if (s[i] >= 'a' and s[i] <= 'm'):
            m = m +s[i].upper()
 elif (s[i] >= 'n' and s[i] <= 'z'):
          m = m +s[i-1]
 elif (s[i].isupper()):
     m = m + s[i].lower()
 else:
     m = m +'&'
print(m)

If column “Fees” contains the data set (5000,8000,7500,5000,8000), what will be the output after the execution of the given query?

SELECT SUM (DISTINCT Fees) FROM student;


Which one of the following is not an aggregate function?


Which one of the following functions are used to find the largest value from the given data in MySQL?


Write outputs for SQL queries that are based on the given table PURCHASE:

TABLE: PURCHASE
CNO CNAME CITY QUANTITY DOP
C01 GURPREET NEW DELHI 150 2022-06-11
C02 MALIKA HYDERABAD 10 2022-02-19
C03 NADAR DALHOUSIE 100 2021-12-04
C04 SAHIB CHANDIGARH 50 2021-10-10
C05 MEHAK CHANDIGARH 15 2021-10-20
  1. SELECT LENGTH(CNAME) FROM PURCHASE WHERE QUANTITY>100;
  2. SELECT CNAME FROM PURCHASE WHERE MONTH(DOP) = 3;
  3. SELECT MOD (QUANTITY, DAY(DOP)) FROM PURCHASE WHERE CITY = ‘CHANDIGARH’;

Based on the table STUDENT given here, write suitable SQL queries for the following:

Roll No Name Class Gender City Marks
1 Abhishek XI M Agra 430
2 Prateek XII M Mumbai 440
3 Sneha XI F Agra 470
4 Nancy XII F Mumbai 492
5 Himanshu XII M Delhi 360
6 Anchal XI F Dubai 256
7 Mehar X F Moscow 324
8 Nishant X M Moscow 429
  1. Display gender-wise highest marks.
  2. Display city-wise lowest marks.
  3. Display the total number of male and female students.

Write suitable SQL query for the following:

Display 7 characters extracted from the 7th left character onwards from the string ‘INDIA SHINING’.


Write suitable SQL query for the following:

Display the position of occurrence of the string ‘COME’ in the string ‘WELCOME WORLD’.


Write suitable SQL query for the following:

Round off the value 23.78 to one decimal place.


Explain the following SQL function using a suitable example.

UCASE()


Explain the following SQL function using a suitable example.

DAYNAME()


Explain the following SQL function using a suitable example.

POWER()


Shreya, a database administrator has designed a database for a clothing shop. Help her by writing answers to the following questions based on the given table:

TABLE: CLOTH
CCODE CNAME SIZE COLOR PRICE DOP
C001 JEANS XL BLUE 990 2022-01-21
C002 T-SHIRT M RED 599 2021-12-12
C003 TROUSER M GREY 399 2021-11-10
C004 SAREE FREE GREEN 1299 2019-11-12
C005 KURTI L WHITE 399 2021-12-07
  1. Write a query to display cloth names in lowercase.
  2. Write a query to display the lowest price of the clothes.
  3. Write a query to count the total number of clothes purchased in medium size.
    OR
    Write a query to count the year-wise total number of clothes purchased.

Which function returns the sum of all elements of a list?


Share
Notifications

Englishहिंदीमराठी


      Forgot password?
Use app×