MySQL Class 10 web Image

       

      SEBA 
      CLASS- 10
      CHAPTER-12
       MySQL 


      OBJECTIVE TYPE QUESTIONS:

      1. Fill in the blanks:

      a. A database is a collection of objects that you need to store and manipulate data such as tables and views.

      b. A Primary key is a column or a set of columns that uniquely identifies each row in the table.

      c. CONSTRAINTS are some rules that help ensure the validity of the data while entering it in the table.

      d. The ALTER TABLE command is used to modify the table structure.

      e. To select a specific row(s), WHERE clause is used in the query.

      f. The like operator clause is used to compare one string value with another.


      2. Write True or False:

      a. The SHOW DATABASES; query will display all the tables. FALSE

      b. The USE command is used to activate a database. TRUE

      c. The query DESC student; will display information on the table ‘student’. TRUE

      d. To display only unique values, the DISTINCT clause is used. TRUE

      e. The two wildcard characters used with the LIKE clause are * and?. FALSE


      3. Choose the correct Options:

      a. The MySQL query that will display the structure of the ‘Customer’ table is DESCRIBE customer;

      b. The command used to modify the content of a table are: ALTER TABLE

      c. The keyword used to sort the column in descending order is DESC

      d. Which one is true about a Primary key column It has unique values

      e. The functions used in GRPUP BY clause are All of these


      Table of Content

      DESCRIPTIVE TYPE QUESTIONS:

      1. Short answer questions:

      a. Write the query to add the primary key constrain to EmpID column of the Employee table.

      Ans:

      The query to add the primary key constrain to EmpID column of the Employee table is:

      ALTER TABLE Employee ADD PRIMARY KEY (EmpID)

      b. Which query will remove the Primary key constraint of AdmissionNo in the Students table?

      Ans:

      The query to remove the Primary key constraint of AdmissionNo in the Students table is:

      ALTER TABLE Student DROP PRIMARY KEY (AdmissionNO(;

      c. Is it possible to add NULL or NOT NULL constraint to a table already loaded with data?

      Ans:

      Yes, it is possible to add NULL or NOT NULL constraint to a table already loaded with data because after the creation of the table it can be done by using MODIFY clause of the ALTER TABLE command.

      d. How will you view all the tables in the database name company?

      Ans:

      To view all the table in the database named the query we use is:

      SHOW DATABASES;

      e. Write the query to active the database name Hospital.

      Ans:

      The query to activate the database name Hospital is:

      USE Hospital;

       

      2. Long answer questions:

      a. What is a constraint? Name any two constraints.

      Ans:

      Constraint is some rules that help ensure the validity of the data while entering it in a table.

      The two constraints in MySQL are:

      i. PRIMARY KEY

      ii. NOT NULL

      b. What is the different between CHAR and VARCHAR data types?

      Ans:

      The difference between CHAR and VARCHAR data types are stated below:

      CHAR

      VARCHAR

      1. Its full form is CHARACTER

      2. It is used to store a fixed length and are padded with space characters to match the specified length.

      3. It can hold a maximum of 255 characters.

      4. It uses static memory location.

      1. Its full name is VARIABLE CHARACTER

      2. It is used to store Values in variable length along with 1 byte or 2-byte length prefix and are not padded with any characters.

      3. It can hold a maximum of 65,535 characters.

      4. It uses dynamic memory location.

      c. What is group function? Explain with an example.

      Ans:

      Group functions are built – in SQL functions that works on a value of a column/expressions and return a single value as a result.

      Example:

      SELECT EmpDept, SUM (EmpSalary), AVG(EmpSalary), MAX (EmpSalary), MIN (EmpSalary)

      FROM employeedetails GROUP BY EmpDept;

      Here, the query displays the sum, average, maximum values of employee salary values in different departments.

      The query:

      SELECT EmpDept, count (*)

      FROM employeedetails GROUP BY EmpDept;

      Here, the query displays the number of records in each department.

      d. What is the different between the following two statements:

      ·         DELETE FROM s1;

      ·         DROP TABLE s1;

      Ans:

      The difference between DELETE FROM s1 and DROP TABLE s1 is that:

      DELETE FROM s1 will remove the specific records from the table s1. Whereas,

      DROP TABLE s1 will delete the entire table of s1 structure.

       

      e. What is the difference between the WHERE and HAVING clause?

      Ans:

      The difference between the WHERE and  HAVING clause are given below.

      WHERE clause

      HAVING clause

      1. it is used in the query to select a specific row.

      2. As per the order of execution of clause, the WHERE clause is executed before the execution of  FROM clause

      3. WHERE clause can be used with GROUPBY clause in the SQL query.

      4. WHERE clause select the rows before grouping of result.

      1. it is used in the query with GROUP BY clause to filter the record set create by the GROUP BY clause.

      2.HAVING clause is executed after groups are created.

      3. HAVING clause does not have combability with GROUPBY clause.

      4. HAVING clause selects the row after grouping.

       

      f. Pair the equivalent SQL statements (that give the same output) from the following:

      i. SELECT * FROM club WHERE salary between 20000 and 30000;

      ii. SELECT * FROM club WHERE salaryIN (20000,30000);

      iii. SELECT * FROM club WHERE salary >= 20000 and salary <= 30000;

      iv. SELECT * FROM club WHERE salary = 20000 or salary = 30000;

      Ans:

      i. SELECT * FROM club WHERE salary between 20000 and 30000;

      ii. SELECT * FROM club WHERE salary >= 20000 and salary <= 30000;


      Table of Content


      APPLICATION BASED QUESTIONS:

      1. Write the MySQL query to create a table Coach with the structure given in the table alongside. Then perform the following functions on this table in MySQL:

      Field Name

      Data Type

      Size

      Constraint

      CoachID

      INTEGER

       

      PRIMARY KEY

      CoachName

      VARCHAR

      15

      NOT NULL

      Sports

      VARCHAR

      20

       

      DateOFApp

      DATE

       

      NOT NULL

      Salary

      INTEGER

       

       

      a. Change the data type of CoachName to VARCHAR(20).

      b. Add a column Gender after CoachName that hold the value ‘M’ or ‘F’.

      c. Remove the primary key constraint from the column CoachID.

      d. What will you do to see that the constraint in (c) has been removed or not?

      Ans:

      Creating the given table:

      CREATE TABLE Coach

      (CoachID INT PRIMARY KEY, CoachName VARCHAR(15) NOT NULL, Sports VARCHAR(20), DateOfApp DATE NOT NULL, Salary INT);

      Ans: a) ALTER TABLE Coach MODIFY CoachName VARCHAR(20);

      Ans: b) ALTER TABLE Coach ADD Gender CHAR (1) AFTER CoachName;

      Ans: c) ALTER TABLE Coach DROP PRIMARY KEY;

      Ans: d) DESC Coach;

      2. Jagriti wrote the following query but it did not give the desired result.

      SELECT MemberName, Amount FROM CLUB

      WHERE Game = NULL OR Game = ‘Table Tennis’;

      Help Jagrita run the query by removing the error and writing the correct query.

      Ans:

      SELECT MemberName, Amount FROM CLUB

      WHERE Game IS NULL OR GAME = ‘Tabel Tennis’;

      3. Consider the table CARDEN given alongside and give the output of the following two queries:

      CarName

      Color

      A-Star

      Red

      C Class

      Silver

      Indigo

      White

      Innova

      Silver

      SX4

      Red

      a. SELECT CarName FROM CARDEN where Color like ‘%r’;

      b. SELECT Color FROM CARDEN WHERE CarName LIKE ‘I%’;

      Ans: a)

      CarName

      C Class

      Innova
       

      Ans: b)

      Color

      White

      Silver

      Explanation:

      Ans: a) The above query means that we should select the CarName from CARDEN table where the name of the color end with letter ‘r’. That’s why C Class and Innova came in the result.

      Ans: b) The above query means that we should select the color from CARDEN table where the name of the Car starts with letter ‘I’. That’s why White and Silver came in the result.

      4. Consider the same table CARDEN and differentiate between the following two queries:

      CarName

      Color

      A-Star

      Red

      C Class

      Silver

      Indigo

      White

      Innova

      Silver

      SX4

      Red

      ·         SELECT Color FROM CARDEN;

      ·         SELECT DISTINCT (Color) FROM CARDEN;

      Ans:

      The first query will show the all colors from table CARDEN but the second will show only the particular colors excluding the duplicates.

      5. Meenal has created a table Sales in MySQL show alongside. She has written the following queries:

      Salesman_No

      Comm

      1001

      200

      1002

      800

      1003

      NULL

      1004

      800

      1005

      NULL

      a. SELECT COUNT(Comm) FROM Sales;

      b. SELECT COUNT (*) FROM Sales:

      Explain the output of the two queries given below.

      Ans:

      The first query will show only 3 values excluding the NULL values whereas the second query will show all the 5 values from the table


      Table of Content