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