Mysql Questions | Questions with Answers in Mysql

 

Mysql Questions | Questions with Answers in Mysql

Q1. What Mysql command will be used to open an already existing database “Library”?

Ans. Use Library;

Q2. The name column of a table members is given below:

Name

Akash Amit Rajiv Preeti

Based on the information, find the output of the following queries:

a.       Select name from members where name like ‘%v’;

b.       Select name from members where name like ‘%e%’;

Ans.

a.

Name Rajiv

b.

Name Preeti

Q3. A table ‘Trains’ in a database has degree 3 and cardinality  8. What is the number of rows and columns in it?

Ans.

Number of Rows : 8

Number of Columns : 3

Q4. Difference between Primary key and Unique.

Ans. Primary key identifies uniquely each record in the table. It cannot contain null and duplicate values.

Unique also identifies uniquely each record in the table. It can contain null values but cannot contain duplicate values.

Q5. Vinay, a student of class X1, created a table ‘Result’.Grade is one of the column of this table. To find the details of students whose grades have not been entered, he wrote the following Mysql query which did not give the desired result.

Select * from result where grade=’Null’

Ans.

Select * from result where grade is null;

Q6. Write Mysql command to display the list of existing databases.

Ans.

Show databases;

Q7. Mr.William wants to remove all the rows from Inventory table to release the storage space, but he does not want to remove the structure of the table. What Mysql statement should he use?

Ans.

Delete from inventory;

Q8. Mr.Mittal is using a table with following columns:

        Name, class, stream,stream_id,stream_name

He needs to display names of students who have not been assigned any stream or have been        assigned a stream_name that ends with ‘Computers’.

He wrote the following command which did not give the desired output.

Select name,class from students where stream_name=null or stream_name like ‘%computers’;

Help Mr.Mittal to run the query by removing the error and write correct query.

Ans.

Select name , class from students where stream_name is null or stream_name like ‘%computers’;

Q9. Ms.Nidhi wants to remove the entire content of a table ‘Backup’ alongwith its structure to release the storage space.What Mysql statement should she use?

Ans.

Drop table backup;

Q10. What is the purpose of Drop Table command in Mysql? How is it different from Delete command.

Ans.

Drop table Command is used to remove the entire content of a table alongwith its structure.

Delete table command is used to remove the rows from the table.

Q11. Consider the table Result given below .

Table: Result

No	Name	Stipend	Average	Division	Subject
1	Amit	400	49	Third	English
2	Sneh	680	34	First	Maths
3	Vijay	500	56	Second	Science
4	Preeti	200	78	First	Accounts
5	Sunita	400	65	First	History
6	Suman	550	45	Third	Computer

a.       To list the names of those students, who have obtained division as first in the ascending order of name.

b.       To display a report listing name,subject and annual stipend received assuming that the stipend column has monthly stipend.

c.       To count the number of students who have either accounts or computer as subject.

d.       Select avg(stipend) from result where division=’third’;

e.       Select count(distinct subject) from result;

f.        Select min(average) from result where subject=’English’;

Ans.

a.       Select name from result where division=’third’ order by name asc;

b.       Select name , subject , stipend*12 as ‘Annual Stipend’ from result ;

c.       Select count(*) from result where subject=’accounts’ or subject=’computer’;

Or you can write as

Select count(*) from result where subject in (‘accounts’,’Computer’);

d.        Avg(stipend)=475

e.       6

f.        49

Q12. What is the purpose of Alter Table command and how is differ from Update command?

Ans.

Alter Command is used to modify the structure of the table. It can be in several ways like adding or dropping column, change the existing column name or definition or datatype, adding or dropping constraints such as primary key, unique, default etc..

For example:

Alter table table_name

Add primary key (id);

Update Command  is used to modify or change the existing record in the table.

For example:

Update table_name

Set salary=20000 where eid=011;

Q13.What is the purpose of Group By Clause and how is differ from Order By Clause?

Ans.

Group By Clause is used to sort or group a number of rows together so as to apply aggregate functions. It is used to sort and also find aggregate functions of each group.

For example : select max(salary) from employee group by department;

Order By Clause is used to only sort the rows in ascending or descending order.

For example : Select * from employee order by salary asc.

Q14. Table Hospital has 4 rows and 5 columns. What is the Cardinality and degree of this table.?

Ans.

The Cardinality is 4

The Degree is 5

Q15. Differentiate between Having and Where with Select query command.

Ans.

The Where with Select Query Command specifies the search condition used to determine the data that will appear in the result table. It is used to specify the rows you want to retrieve.

For example: Select * from student where grade=’A’;

Having Clause restricts grouped rows that appear in the result table. Groups are specified with the group by clause. You can combine search conditions with the And, Or, Not Operations.

For example: Select avg (fees) from student group by grade having sum(fees)>1500;

Q16. How many types of functions are there in sql and what are they?

Ans.

Sql functions can be divided broadly into four categories:

a.       Character Functions

b.       Data Conversion Functions

c.       Date Functions

d.       Numeric Functions

Q17. Give the examples of character functions.

Ans. Character Functions :

LCase() : It is used to convert all characters into lower case character.

UCase() : It is used to convert all the characters into upper case character.

Q18. Give at least four examples of numeric functions.

Ans. Numeric Functions: Round(),Mod(),Power()

Q19. Which function returns the remainder of numeric expression.

Ans. Mod() function

Q20. Write the function which returns the character string by converting each character to lower character.

Ans. Lower() or LCase() function.

Q21. Which function returns the length of a string.

Ans. Length() function

Q22. Write the function name which  returns specified number of character from a string.

Ans. SubString() function

Q23. How will you count number of characters in a character expression.

Ans. Length() function

Q24. How will you get computer date?

Ans. Sysdate() and Curdate() function

Q25. How will you remove leading and trailing blanks from a character string expression P, where P=”Amit#Jain####” (Here # denote as blank spaces).

Ans. Trim() function is used to remove leading and trailing blanks.

Select trim(“Amit#Jain####”);

Q26. What will be the output of

a.       Select round(124.44)+mod(1200.87,3);

b.       Select mod(30.500,5)+round(100.50,1);

Ans.

a.       124.87

b.       101

Q27. Table : Employee

Empno	Ename	Job	MGR	Hiredate	Sal	Comm	Dept
7369	Amit	Clerk	7902	1980-12-17	2800	Null	20
7499	Sneh	Salesman	7698	1981-02-20	3600	300	30
7521	Preeti	Salesman	7698	1981-02-22	5250	500	30
7566	Vinay	Manager	7839	1981-04-02	4975	Null	20
7654	Deepak	Salesman	7698	1981-09-29	6250	1400	30
7698	Vishal	Manager	7839	1981-05-01	5850	Null	30
7782	Neeraj	Manager	7839	1981-06-09	2450	null	10

Write some sql commands

a.       Simple Select query questions.

·         To select all the columns of the above table.

·         To list the name and employee number from the above table.

·         To list all names, hiredate and salary of all employee.

Ans:

a.        

·         Select * from employee;

·         Select Ename, Empno from employee;

·         Select Ename,Hiredate, Sal from employee;

Q28. 

Constraint in sql | Sql Constraints

Constraints in sql | Sql Constraints

Constraints are the rules that we can apply on the type of data in a table.

For Practice Question www.techquestions.in

There are 6 constraints in sql.

1. Primary key

2. Foreign key

3. Unique 

4. Default

5. Check

6. Not Null

Primary key : A  primary key is a column or a group of  columns that uniquely identifies each row in a table.

How can we define primary key with create command

Method 1: 

create table emp

( empid int primary key, empname varchar(20));

Method 2:

create table emp (empid int, empname varchar(20), primary key(empid));

Method 3:

create table emp(empid int,empname varchar(20), Constraint pk primary key(empid));

primary key with alter command

Method 1.

Alter table emp

add primary key (empid);

Method 2.

Alter table emp

Add constraint pk primary key(empid);

How to drop a primary key

Alter table emp

drop primary key

Note: As we know that a single table can have only one primary key so that for deleting a primary key we do not need to mention the column name.

Screenshot:

Primary Key in sql

Foreign Key :

 A foreign key refers to a primary key in another table. A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key. Foreign key is also known as referential integrity.

• A table can have more than one foreign key.

• Foreign key column can have duplicate values.

How can we define foreign key with create command

create table employee

( employeeid int primary key, employeename varchar(20));

create table department
(departmentid int prmary key, 
empid int,
foreign key(empid) references employee(employeeid)
);

or

create table department
(departmentid int prmary key, 
empid int,
constraint fk foreign key(empid) references employee(employeeid)
);

Foreign key with on delete cascade on update cascade

create table department
(departmentid int prmary key, 
empid int,
foreign key(empid) references employee(employeeid) on delete cascade
);

Note: The foreign key in this case uses ON DELETE CASCADE to specify that when a record is deleted from the parent table, child records with a matching employeeid value should be removed automatically as well.

create table dept
(deptid int prmary key, 
empid int,
foreign key(empid) references employee(employeeid) on delete cascade on update cascade

);

Note: If you write ON UPDATE CASCADE also, while defining foreign key constraint then in case a row is updated in parent table, all its related rows in the child table will automatically be updated.

Foreign key with on delete cascade on update cascade

As see above.. After inserting values both the tables. Lets check update cascade how works.

We have these two tables with given values.

Emp table and EmpDept table before on update cascade

We update the value of employeeid from 101 to 1101.

Emp table and EmpDept table after on update cascade.

Foreign key with alter command

Alter table dept

add foreign key(employeeid)
references employee(employeeid);

Alter table dept

add constraint fk foreign key(employeeid)
references employee(employeeid);

How to drop a foreign key

Alter table emp

drop foreign key fk;

Unique Key | Unique Constraint : This constraint ensures that no two rows have the same value in the specified columns.

A column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key. 

We can apply unique constraint more than one column. It means multiple unique constraints can be defined on a table.

• A table can have more than one unique constraint.
• Unique Constraint is almost similar to primary key. Primary key does not have duplicate and null values whereas Unique key does not have duplicate values but can have null values.

How to create Unique key ?

Create table students(

Rollno int primary key,

admissionNumber int unique,

name varchar(25));

Create table staff
(
teacher_id int,
teacher_name varchar(30),
contact_number varchar(12),
constraint uc unique(contact_number)
);

 We can also create multiple unique constraints in a one table.

Create table staff
(
teacher_id int unique,
teacher_name varchar(30),
contact_number varchar(12) unique
);

How to create Unique key with alter command ?

Alter table staff

add  unique(teacher_id);

or

Alter table staff

add constraint uc unique(teacher_id);

How to drop Unique Constraint ?

You can drop a Unique Constraint with the 'Index' keyword if and only if you have used constraint name.

Alter table staff

drop index uc ;      // Here 'uc' is  the unique constraint name.

Default Constraint

A default value can be specified for a column using the Default clause. When a user does not enter a value for the column(having default value), automatically the defined default value is inserted in the field.

Create table employee

(

ecode int primary key,

ename char(20) ,

grade char(2) default 'B'

);

Note According to the above command , if no value is provided for grade, the default value of 'B' will be entered. The datatype of the default value has to be compatible with the datatype of the column to which it is assigned. Insertion of NULL (as default value) is possible only if the column definition permits.Not Null columns cannot have NULL as default. A column can have only one default value. 

How to create Default Constraint with alter command ?

Alter table employee

alter grade set default 'B';

How to drop default constraint.

Alter table employee

alter grade drop default;

Note you can apply default constraint more than one in a table if you want to apply Default Constraint on Integer type column you have to write as 

Create table employee

(

ecode int default 0,

ename char(20) ,

grade char(2) default 'B'

);

Check Constraint

A Check  constraint controls the values in the associated column.It determines whether the value is valid or not from a logical expression.

It limits values that can be inserted into a column of a table. 

How to create Check Constraint ?

Create table items

(

icode int primary key,

descprition varachar(20),

qty int Check (qty>2000),

price int

);

or 

Create table items

(

icode int primary key,

descprition varachar(20),

qty int ,

price int,

Check (qty>2000)

);

or

Create table voter

(

id int,

name varchar(20),

age int,

city varchar(20),

constraint chk check(age>=18 and city='Delhi')

);

You can apply more than one check constraint in a table.

Create table product

(

pcode int primary key,

pname varchar(20),

price int,

qty1 int,

qty2 int,

check (qty1>qty2)

);

This statement compares two columns qty1 and qty2 , thus, these two columns must be defined before the check constraint.

Check Constraint can be consist of :

• A list of consist expressions specified using IN 

       Create table product

       (

        code int,

       price int,

       city varchar(20), 

       qty int,

       check(city in('Delhi','Mumbai','Agra')) 

        );

• Range of constant expressions specified using BETWEEN. The upper and lower boundary values are included in the range.

       Create table product

       (

        code int,

       price int, 

       city varchar(20)  

       qty int,

       check(price between 500 and 1000)

        );

• A pattern specified using LIKE .

       Create table product

       (

        code int,

       price int,

       city varchar(20)  

       order char(10) ,

       check (order like '--%')

        );

• Multiple conditions using OR,AND etc.

       Create table product

       (

        code int,

       price int,

       city varchar(20),  

       qty int,

       check(city='delhi' and price>500)

        );

How to create Check Constraint with alter command ?

Alter table voter

add check(age>=18);

or 

Alter table voter

add constraint chk check(age>=18);

How to drop Check constraint.

Alter table employee

drop check chk;

Here chk is a Check Constraint name.


Note:All the queries are executed without checking the condition specified through CHECK constraint.
The reason being Mysql ignores CHECK constraint internally.

Not Null Constraint

A column with a NOT NULL constraint cannot have null values. This constraint ensures that the null values(empty values) are not permitted for a specified column. This constraint can be defined at the column level but not at the table level.

By default, Null is assumed, and null values are permitted in the column.

How to create NOT NULL Constraint ?

Create table Book

(

id int not null,

Title varchar(20) not null,

price int

);

How to use NOT NULL Constraint with alter command ?

Alter table Book

modify price int not null;

How to drop Check constraint.

You can drop NOT NULL constraint directly , you can modify it.

Alter table book

modify price int;