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