Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 12

Q1. Write the output of the following on the basis of given table : Product

  

1. Select max(price) from product;

2. Select avg(price) from product;

3. Select min(qty) from product;

4. Select count(*) from product;

5. Select count(Qty) from product;

6. Select distinct(price) from product;

7. Select count(distinct(price)) from product;

8. Select price * Qty from product;

9. Select sum(price) from product;

10. Select sum(price) where Qty > 30;

 SOLUTIONS

Q1. Write the output of the following on the basis of given table : Product

  

1. Select max(price) from product;

Ans. 320

2. Select avg(price) from product;

Ans. 218

3. Select min(qty) from product;

Ans. 17

4. Select count(*) from product;

Ans. 5

5. Select count(Qty) from product;

Ans. 5

6. Select distinct(price) from product;

Ans. 

240

300

320

130

100

7. Select count(distinct(price)) from product;

Ans. 5

8. Select price * Qty from product;

Ans. 

5520

7200

13760

4160

1700

9. Select sum(price) from product;

Ans. 1090

10. Select sum(price) where Qty > 30;

Ans. Error

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 11

Q1. What is the degree and cardinalty of table who has 6 columns and 10 rows?

Q2. Identify the errors in the following query

a. Select * from Student where Name = NULL;

b. Select max(salary) from employee group by designation where DOJ > "2020-02-02";

c. Select * from student where name is = "Amit";

d. Select * from employee where salary between 1000, 2000;

e. Select * from employee where name = "A%";

f. Select Name , Subject from student where average >20 and < 30;

g. Select * from Student where Name = "Amit" and Name ="Sumit";

h. Select highest(Salary) from employee;

i. Select all from student;

j. Update table student set Name = "Sunita" where admno = 1234;

SOLUTIONS

Q1. What is the degree and cardinalty of table who has 6 columns and 10 rows?

Ans. Degree - 6 and Cardinality - 10

Q2. Identify the errors in the following query

a. Select * from Student where Name = NULL;

Ans. Select * from Student where Name is NULL;

b. Select max(salary) from employee group by designation where DOJ > "2020-02-02";

Ans. Select max(salary) from employee group by designation having DOJ > "2020-02-02";

c. Select * from student where name is = "Amit";

Ans. Select * from student where name  = "Amit";

d. Select * from employee where salary between 1000, 2000;

Ans. Select * from employee where salary between 1000 and 2000;

e. Select * from employee where name = "A%";

Ans. Select * from employee where name  like "A%";

f. Select Name , Subject from student where average >20 and < 30;

Ans. Select Name , Subject from student where average >20 and average < 30;

g. Select * from Student where Name = "Amit" and Name ="Sumit";

Ans. Select * from Student where Name = "Amit" or Name = "Sumit";

h. Select highest(Salary) from employee;

Ans. Select max(Salary) from employee;

i. Select all from student;

Ans. Select * from Student;

j. Update table student set Name = "Sunita" where admno = 1234;

Ans. Update student set Name = "Sunita" where admno = 1234;

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 10

Q1. What is the difference between drop and delete command?

Q2. Differentiate between Alter and Update command.

Q3. What is group by clause?

Q4. Write the output of the following:

1. Select 75 + 3*2 
2. Select 23 + 56%5
3. Select power(2,3)
4. Select round(10.237,2)
5. Select round(12.3454,2)
6. Select round(12.3444,2)

Q5. Write the query on the basis of the following table :STUDENT 

Name, Admno, Subject, Average, Position

1. Display all records in ascending order by name

      

      2. Display details of students whose position is "First" and average greater than 70

 

      

     3. Display details of "Amit", "Sumit" and "Ashish" (using IN operator)

     

    4. Display Name and Subject  of student whose position is "First"

    

   5. Display the total number of records present in above table.

   6. Display the maximum average.

 

   7. Display name of student who got maximum average.

   8. Insert he following record:

      "Shikha", 1221, "Physics", 70, "Second"

 

SOLUTIONS

Q1. What is the difference between drop and delete command?

Ans. Drop command delete the data as well as structure of table permanently from database

        while delete command delete only the data from the table.

Q2. Differentiate between Alter and Update command.

Ans. Alter command is used to change the structure of table and Update command is used 

       to modify the data of table.

Q3. What is group by clause?

Ans. This clause is used to arrange same data in groups using some group functions 

        like Sum, average etc.

Q4. Write the output of the following:

1. Select 75 + 3*2 ------------------------------------Ans. 81
2. Select 23 + 56%5----------------------------------Ans. 24
3. Select power(2,3)----------------------------------Ans. 8
4. Select round(10.237,2)----------------------------Ans. 10.24
5. Select round(12.3454,2)---------------------------Ans. 12.35
6. Select round(12.3444,2)---------------------------Ans. 12.34

Q5. Write the query on the basis of the following table :STUDENT 

Name, Admno, Subject, Average, Position

1. Display all records in ascending order by name

      Ans. Select * from Student order by Name;

      2. Display details of students whose position is "First" and average greater than 70

 

      Ans. Select * from student where position ="First" and Average >70;

     3. Display details of "Amit", "Sumit" and "Ashish" (using IN operator)

     Ans. Select * from Student where Name IN("Amit", "Sumit", "Ashish");

    4. Display Name and Subject  of student whose position is "First"

    Ans. Select Name, Subject from student where position = "First";

   5. Display the total number of records present in above table.

   Ans. Select count(*) from Student.

   6. Display the maximum average.

 

   Ans. Select max(Average) from Student;

  7. Display name of student who got maximum average.

   Ans. Select Name, max(Average) from Student;

  8. Insert he following record:

      "Shikha", 1221, "Physics", 70, "Second"

  Ans. Insert into student values ("Shikha", 1221, "Physics", 70, "Second");

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 9

Q1. What do you mean by aggregate function?

Q2. Which keyword is used to arrange records in increasing or decreasing order?

Q3. Which function returns the total number of records in a table?

Q4. Select count( * ) from student; return 5

       Select count(fee) from student; return 4

why different output in above two queries?

Q5. ____________ function return the average value of a numeric column.

Q6. Which function returns the sum of numeric column?

Q7. _______ keywords removes duplicates records from the table.

Q8. Write a query to display all the records of table student whose name starts from "A"

Q9. Identify the error in the following statement.

Select * from student where name = null

Q10. Which function return the minimum value from column fee of table student?

 SOLUTIONS

Q1. What do you mean by aggregate function?

Ans. A function which work on multiple values and return a single value.

Q2. Which keyword is used to arrange records in increasing or decreasing order?

Ans. Order by

Q3. Which function returns the total number of records in a table?

Ans. Count( )

Q4. Select count( * ) from student; return 5

       Select count(fee) from student; return 4

why different output in above two queries?

Ans. Different output shows that there must be one null value in column fee.

Q5. ____________ function return the average value of a numeric column.

Ans. avg( )

Q6. Which function returns the sum of numeric column?

Ans. sum( )

Q7. _______ keywords removes duplicates records from the table.

Ans. distinct

Q8. Write a query to display all the records of table student whose name starts from "A"

Ans. Select * from student where name like "A%";

Q9. Identify the error in the following statement.

Select * from student where name = null

Ans. Select * from student where name is null; 

Q10. Which function return the minimum value from column fee of table student?

Ans. min( )

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 8

Q1. Write the output of the following:

Select 76 + 75%4 from dual;

Q2. Write the queries of the following:

                                                Table : Student

1. Display all the records of table student.
2. Display Roll Number, Name and Class of table Student
3. Display records of students of class X.
4. Display details of Sumit.
5. Display records of student paying fees less than 3000.
6. Display fee of Amit
7. Display Class and percentage of  Pushkar.
8. Delete record of Amit
9. Display the structure of table student
10. Insert the following record in table student

              5, "Suman", 'X', 3000, 70

SOLUTIONS

Ans 1.  79

Ans 2.

1. Select * from student;
2. Select RollNo, Name , Class from student;
3. Select * from student where class = 'X';
4. Select * from student where Name = 'Sumit';
5. Select * from students where fee < 3000;
6. Select fee from student where name = "Amit";
7. Select class, percentage from student where name = "Pushkar"
8. Delete from student where name = "Amit"
9. Desc student;
10. Insert into student values(5, "Suman", "X", 3000, 70);

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 7

Q1. Which command is used to delete data from the table?

Q2. Which command is used to add column in a table?

Q3. Write a query to add the column DOB of data type date in table Student.

Q4. Write a query to add new column "Grade" of data type varchar(2) in table 'Student' 

       with default value "A".

Q5. Write a query to change the data type of above added column (Grade) to varchar(4).

Q6. Write a query to delete column Grade from table student.

Q7. ___________Command is used to delete table completely/permanently.

Q8. Write a query to delete the table "Emp" permanently.

Q9. Delete from emp;

       The above command will delete all the records from table "emp". (True/False)

Q10. Write a query to display all the records from table "Student"

        

SOLUTIONS

Q1. Which command is used to delete data from the table?

Ans. Delete

Q2. Which command is used to add column in a table?

Ans. Alter

Q3. Write a query to add the column DOB of data type date in table Student.

Ans. Alter table student add DOB date;

Q4. Write a query to add new column "Grade" of data type varchar(2) in table 'Student' 

       with default value "A".

Ans. Alter table student add( Grade char(2) default "A");

Q5. Write a query to change the data type of above added column (Grade) to varchar(4).

Ans. Alter table student modify (Grade varchar(4));

Q6. Write a query to delete column Grade from table student.

Ans. Alter table Student drop Grade;

Q7. ___________Command is used to delete table completely/permanently.

Ans. Drop table

Q8. Write a query to delete the table "Emp" permanently.

Ans. Drop table Emp;

Q9. Delete from emp;

       The above command will delete all the records from table "emp". (True/False)

Ans. True

Q10. Write a query to display all the records from table "Student"

Ans. Select * from Student;

        

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 6

Q1. ___________ command is used to remove database completely.

Q2. Which command is used to show the structure of the table.

Q3. Name the columns which are visible when we execute the following command.

desc book;

Q4. Write the command to create the following table : "Student"

Field Name                        Data type                        Constraint

Rollno                                Integer (5)                      Primary Key

Sname                               Varchar(30)             

Contactno                          Char(10)                         Not Null

Q5. Write query to insert the following record in above created table.

      Roll number --  1, Name--Amit, Contact number-- 1234567890

Q6. Write a query to insert the following values only.

       Roll number --  2 , Contact number-- 11111111

Q7. Is Null value is equivalent to Zero?

Q8. What do you mean by Null in MySQL?

Q9. Which command is used to modify data in table?

Q10. Write a query to modify the Contactno to 98789878 whose roll number is 1

         in table student.(given above)

 SOLUTIONS

Q1. ___________ command is used to remove database completely.

Ans. Drop

Q2. Which command is used to show the structure of the table.

Ans. desc or describe.

Q3. Name the columns which are visiblewhen we execute the following command.

desc book;

Ans. Columns are:

Field,      Type          Null,          Key,          Default,          Extra

Q4. Write the command to create the following table : "Student"

Field Name                        Data type                        Constraint

Rollno                                Integer (5)                      Primary Key

Sname                               Varchar(30)             

Contactno                          Char(10)                         Not Null

Ans. Create table student(Rollno integer(5) not null primary key, Sname varchar(30), Contactno char(10) not null);

Q5. Write query to insert the following record in above created table.

      Roll number --  1, Name--Amit, Contact number-- 1234567890

Ans. Insert into student values(1, "Amit", "123456780");

Q6. Write a query to insert the following values only.

       Roll number --  2 , Contact number-- 11111111

Ans. Insert into student(Rollno, Contactno) values(2,'1111111');

Q7. Is Null value is equivalent to Zero?

Ans. No

Q8. What do you mean by Null in MySQL?

Ans. Null means a value which is unavailable or in other words 

       we can say Null means no value.

Q9. Which command is used to modify data in table?

Ans. Update

Q10. Write a query to modify the Contactno to 98789878 whose roll number is 1

         in table student.(given above)

Ans. Update student set Contactno='98789878' where Rollno = 1;

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 5

Q1. What do you mean by keyword in MySQL?

Q2. Identify the keyword from the following query.

Select * from book;

Q3. All statements in MySQL are terminated by __________.

Q4. We can create ____________ (database/tables)inside ___________(database/tables).

Q5. SQL is a case sensitive language(T/F)

Q6. _________ statement is used to show all the existing databases in server.

Q7. Which statement is used to show all existing table in database.

Q8. Write statement to open a database named "student".

Q9. Name the command used to create database.

Q10. Write statement to create database named "book"

 SOLUTION

Q1. What do you mean by keyword in MySQL?
Ans. Keyword refers to a word which has special meaning in MySQL.

Q2. Identy the keyword from the following query.
Select * from book;
Ans. Keywords are : select and from

Q3. All statements in MySQL is terminated by __________.
Ans. Semicolon(;)

Q4. We can create ____________ (database/tables)inside ___________(database/tables).

Ans. tables, database

Q5. SQL is a case sensitive language(T/F)

Ans. False

Q6. _________ statement is used to show all the existing databases in server.

Ans. Show databases;

Q7. Which statement is used to show all existing table in database.

Ans. Show tables;

Q8. Write statement to open a database named "student".

Ans. use student;

Q9. Name the command used to create database.

Ans. Create database

Q10. Write statement to create database named "book"

Ans. create database book;

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 4

Q1. In MySQL, date values to be enclosed in { } or in single quotation 

      marks(True/False)

Q2. ______________ is the format of date in MySQL.

Q3. Data type of "name" field in a table is char(25). How many bytes will be

      occupied by values "Ram" and "Rohan Kumar"?

Q4. Time data type is used to store time in ________ format.

Q5. Varchar is a fixed length data type.(True/False)

Q6. Name the command which is used to close MySQL.

Q7. Which data type in MySQL is used to store logical values?

Q8. Out of char, varchar and memo, which data type is used to store large 

      amount of data?

Q9. Which data type in MySQL is used to store images, animations,

      clips etc.

Q10. Write the appropriate data types for the following fields.

1. DateofBirth
2. Salary
3. Name
4. Address
5. Phonenumber

 SOLUTIONS

Q1. In MySQL, date values to be enclosed in { } or in single quotation 

      marks(True/False)

Ans. True

Q2. ______________ is the format of date in MySQL.

Ans. yyyy/mm/dd

Q3. Data type of "name" field in a table is char(25). How many bytes will be

      occupied by values "Ram" and "Rohan Kumar"?

Ans. 25

Q4. Time data type is used to store time in ________ format.

Ans. hh:mm:ss

Q5. Varchar is a fixed length data type.(True/False)

Ans. False

Q6. Name the command which is used to close MySQL.

Ans. quit

Q7. Which data type in MySQL is used to store logical values?

Ans. Boolean

Q8. Out of char, varchar and memo, which data type is used to store large 

      amount of data?

Ans. memo

Q9. Which data type in MySQL is used to store images, animations,

      clips etc.

Ans. BLOB or RAW

Q10. Write the appropriate data types for the following fields.

1. DateofBirth
2. Salary
3. Name
4. Address
5. Phonenumber

Ans. 

1. Date
2. Any numeric data type preferable numeric or decimal.
3. Char or Varchar
4. Char or Varchar
5. Char or Numeric

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 3

Q1. Write two advantages of SQL.

Q2. MySQL is an ___________Software. (open source/Proprietary)

Q3. Name two types of SQL commands.

Q4. What is the difference between DDL and DML Commands?

Q5. Identify the DDL and DML commands from the following.

1. Create
2. Alter
3. Insert
4. Update
5. Drop
6. Delete
7. Select
8. Grant
9. Revoke

Q6. What do you mean by data type?

Q7. Name two numeric data type in MySQL.

Q8. Name two String Data type in MySQL.

Q9. Which data type is used for "Date of birth" field in Student table.

Q10. What is the difference between Char and Varchar.

SOLUTIONS:

Q1. Write two advantages of SQL.

Ans. 

• It is very easy to learn
• It is not a case sensitive language.

Q2. MySQL is an ___________Software. (open source/Proprietary)

Ans. open source

Q3. Name two types of SQL commands.

Ans

1. DDL
2. DML

Q4. What is the difference between DDL and DML Commands?

Ans. 

DDL	DML
It stands for Data Definition Language	It stands for Data Definition Language
These commands allow   to perform tasks related   to the structure of the table	These commands are used to manipulate data

       

Q5. Identify the DDL and DML commands from the following.

1. Create
2. Alter
3. Insert
4. Update
5. Drop
6. Delete
7. Select
8. Grant
9. Revoke

Ans

1. DDL
2. DDL
3. DML
4. DML
5. DDL
6. DML
7. DML
8. DDL
9. DDL

Q6. What do you mean by data type?

Ans. Data type refers to the type of data we are entering in the column of the table.

Q7. Name two numeric data type in MySQL.

Ans. Integer and Smallint

Q8. Name two String Data type in MySQL.

Ans. Char and Varchar

Q9. Which data type is used for "Date of birth" field in Student table.

Ans. Date

Q10. What is the difference between Char and Varchar.

Ans. Char is fixed length data type while Varchar is variable length data type.

 

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 2

Q1. What do you mean by data redundancy?

Q2. What do you mean by data inconsistency?

Q3. Define the term data model.

Q4. Name 3 types of data models.

Q5. A ___________ is a collection of logically related data.

Q6. Answer the following questions on the basis the given table.

a. How many attributes are there in above table?

b. How many tuples are there in above table?

c. What is the degree of above table?

d. What is the cardinality of above table?

e. Name the primary key in the table.

Q7. Write any five attributes of book entity.

Q8. Write any two advantages of database.

Q9. What is the purpose of creating primary key in table.

Q10. Which field you choose as primary key out of following in "Employee" table

1. Emp_id
2. Emp_name
3. Emp_salary
4. Emp_desig

Ans 1.                                                                                     

 

Computer Science - Informatics Practices Class XI/XII MySQL Test Series Part 1

Q1. Write full form of DBMS.

Q2. Define the following term

1. Primary Key
2. Foreign Key
3. Candidate Key
4. Alternate Key

Q3. A group of rows and columns is called _______________

Q4. Candidate Key - Primary key = ___________________

Q5. Give two examples of DBMS software.

Q6. What is the difference between Data and Information?

Q7. A primary key is one of the candidate key. (True/False)

Q8. What do you mean by degree and cardinality in DBMS?

Q9. What is the alternate name of column in table?

Q10. What is the alternate name of row in table?

SOLUTIONS:

Q1. Write full form of DBMS.

Ans. Database Management System

Q2. Define the following term

1. Primary Key
2. Foreign Key
3. Candidate Key
4. Alternate Key

Ans. 

1. A field which uniquely identifies each and every record.
2. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. It is used to link two tables.
3. Those fields which can act as a primary key are called candidates key

Q3. A group of rows and columns is called _______________

Ans Table

Q4. Candidate Key - Primary key = ___________________

Ans. Alternate Key

Q5. Give two examples of DBMS software.

Ans. Oracle, MS Access, MySQL

Q6. What is the difference between Data and Information?

Ans. Raw facts and figures is called Data. 

        Processed data is called information

Q7. A primary key is one of the candidate key. (True/False)

Ans. True

Q8. What do you mean by degree and cardinality in DBMS?

Ans. Number of rows in a table is called cardinality

        Number of columns in a table is called degree.

Q9. What is the alternate name of column in table?

Ans. Attribute and Field 

Q10. What is the alternate name of row in table?

Ans. Record and Tuple