TABLE CREATION AND DATA MANIPULATION COMMANDS || MYSQL || CBSE

 TABLE CREATION AND DATA MANIPULATION COMMANDS

Short Answer Type Questions 

Q.1     Using SQL statements in MySQL, create the tables identified bellow in the following order.

           Database Name: Univ. 

       Create database if needed. (Underlined Columns depict primary key)

           Campus   (CampusID, CampusName, Street, City, State, Pin, Phone, 

CampusDiscount)

           Position            (PositionID, Position, YearlyMembershipFee)

           Members        (MemberID, Lastname, FirstName, CampusAddress, CampusPhone, 

CampusID,  PositionID, ContractDuration)

           Foreign Keys                   CampusID à Campus(CampusID)

                                                       PositionID àPosition(PositionID)

Ans: mysql>CREATE DATABASE Univ;   mysql>USE Univ;

        mysql>CREATE TABLE Campus(CampusID VARCHAR(5) PRIMARY KEY,

                                                    CampusName VARCHAR(20),

                                                           Street VARCHAR(20),

                                                              City VARCHAR(20),

                                                            State VARCHAR(15),

                                                    Pin INT(6),

                                                           Phone VARCHAR(13),

                                                    CampusDiscount INT(2));

        mysql>CREATE TABLE Position (PositionID VARCHAR(4) PRIMARY KEY,

                                                         Position VARCHAR(10),

                                                    YearlyMemberShipFee INT(4));

      mysql>CREATE TABLE Members (MemberID VARCHAR(5) PRIMARY KEY,

                                                      LastName VARCHAR(15),

                                                     FirstName VARCHAR(15),

                                                    CampuAddress VARCHAR(30),

                                                    CampusPhone VARCHAR(13),

                                                    CAMPUSID VARCHAR(5) REFERENCES Campus(CampusID),

                                                     PositionID VARCHAR(4) REFERENCES Position(PositionID),

                                                    ContractDuration INT(2));

Q.2     Write SQL commands to perform the following tasks –   (a)  Create table Employee with the following structure:

Name of Column	ID	First_Name	Last_Name	User_ID	Salary
Type	Number(4)	Varchar(30)	Varchar(30)	Varchar(10)	Number(9,2)

            Ensure the following specification in created table:

 ID should be declared as Primary Key

 User_ID shold be unique

 Salary Must be greater than 5000

 First_Name and Lst_Name must not remain Blank

Ans: mysql>CREATE TABLE Employee (ID NUMBER(4) PRIMARY KEY,

                                                    First_Name VARCHAR(30) NOT NULL,

                                                    Last_Name VARCHAR(30) NOT NULL,

                                                        User_ID VARCHAR(10) UNIQUE,

                                                          Salary NUMBER(9,2));         

              (b)        Create another table Job with following specification:

Name of Column                                Type Job_ID                                        Number(4)

                    Job_des                                                                     Varchar(30)

                     Alloc_on                                                                          Date

                    Due_on                                                                           Date

                     Emp_id                                                                      Number(4)

Ensure the following specification in created table:

 Job_ID should be declared as Primary Key

 Job_des, Alloc_on, Due_on cannot be left blank

 Emp_id is foreign key here that us related to ID column of earlier created table Employee.

Ans: mysql>CREATE TABLE Job (Job_ID NUMBER(4) PRIMARY KEY,

                                                        Job_des VARCHAR(30) NOT NULL,

                                               Alloc_on DATE NOT NULL,

                                          Due_on DATE NOT NULL,

                                                        Emp_id NUMBER(4) REFERENCES Employee(ID));

(a)  Show the structure of the table employee.

Ans: mysql>DESC Employee;

(b)  Show the structure of the table job.

Ans: mysql>DESC Job;

(c)  Insert 10 records into the table employee as well as Job.

Ans: mysql> INSERT INTO Employee VALUES(1,  ‗Amit‘, ‘Kumar‘, ‘E1001‘,20000);  Same remaining 9 values can be inserted into Employee Table.

 mysql> INSERT INTO Job VALUES(1001,  ‗Manager‘, ‘12-25-2016‘, ‘12-28-2017‘, 1);  Same remaining 9 values can be inserted into Job Table.  

(d)  For record with ID 4 update record with Last_Name, User_ID and Salary. Ans:  mysql>UPDATE Employee SET Last_Name=‘Singh‘  WHERE ID=4;    mysql>UPDATE Employee SET Salary=25000  WHERE ID=4;    mysql>UPDATE Employee SET User_ID=‘E1004‘  WHERE ID=4; (e)  Make the changes permanent.

Ans: mysql>COMMIT

(f)  Modify the salary and increase it by 1000, for all who get salary less than 6000.

Ans: mysql>UPDATE Employee SET Salary = Salary+1000 WHERE Salary<6000; (g) Add one new column in table Employee named „Phone‟. Ans: mysql>ALTER TABLE Employee ADD(Phone VARCHAR(13)); (h)Provide the phone numbers to all the employees.

Ans: mysql>INSERT INTO Employee (Phone) VALUES(‗+918888888888‘);             Same remaining 9 values can be inserted into Employee Table.

(i) Delete the Employee record having First_Name as Manish. Ans: mysql>DELETE FROM Employee WHERE First_Name=‘Manish‘; (j) Remove employee table permanently. 

Ans: mysql>DROP TABLE Employee;

(k) Count the total records in the table employee.

Ans: mysql>SELECT Count(*) FROM Employee;

Q.3     What are the different divisions of SQL and commands? Give examples. Ans: SQL commands can be divided into following categories – 

(i)          Data Definition Language (DDL) Commands. e.g. CREATE TABLE, ALTER TABLE, DROP TABLE etc.

(ii)         Data Manipulation Language (DML) Commands. e.g. INSERT INTO, DELETE FROM, UPDATE, SELECT etc.

(iii)        Transaction Control Language (TCL) Commands.e.g. ROLLBACK, COMMIT, SAVEPOINT

etc.

Q.4            What is foreign key? How do you define a foreign key in your table?

Ans: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

           To define a foreign key in a table we have to use REFERENCES keyword as follows  -   mysql>CREATE TABLE Job (Job_ID NUMBER(4) PRIMARY KEY,

                                                        Job_des VARCHAR(30) NOT NULL,

                                               Alloc_on DATE NOT NULL,

                                          Due_on DATE NOT NULL,

                                                       Emp_id NUMBER(4) REFERENCES Employee(ID));

          In the above example Emp_id is a foreign key which references the ID field of table Employee. Q.5          How foreign key command is different from Primary Key command?

Ans: A primary key is a special key in a relational database that acts as a unique identifier for each record meaning it uniquely identifies each row/record in a table and its value should be unique for each row of the table. A foreign key, on the other hand, is a field in one table that link two tables together. It refers to a column or a group of columns that uniquely identifies a row of another table or same table. 

A primary key is a combination of UNIQUE and Not Null constraints so no duplicate values can be allowed to have in a primary key field in a relational database table. No two rows are allowed to carry duplicate values for a primary key attribute. Unlike a primary key, foreign key can contain duplicate values and a table in a relational database can contain more than foreign key. Q.6 How is Foreign Key commands related to Primary Key?

Ans: A foreign key refers to a field or a collection of fields in a database record that uniquely identifies a key field of another database record in some other table. In simple terms, it establishes a link between records in two different tables in a database. It can be a column in a table that points to the primary key columns meaning a foreign key defined in a table refers to the primary key of some other table. References are crucial in relational databases to establish links between records which is essential for sorting databases. Foreign keys play an important role in relational database normalization especially when tables need to access other tables. Q.7 How do you enforce business rules on a database?

Ans: We can enforce Business Rules in Relational Databases Using Constraints. Constraints are restrictions over a column. Or we can say that constraint is a check or condition applicable on a field or set of fields. These are of following types – 

(i)                  Unique

(ii)                 Primary Key

(iii)               Default

(iv)               Check

(v)                 Foreign Key

Q.8       What are table constraints? What are column constraints? How these two are different?

Ans: Table constraints apply to groups of one or more columns whereas column constraints apply

only to Individual column. 

Q.9       What is default value? How do you define it? What is the default value of column for which no default value is defined?

Ans: A default value can be specified for a column using 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. e.g.

                 mysql>CREATE TABLE Employee (ID NUMBER(4) PRIMARY KEY,

                                                    First_Name VARCHAR(30) NOT NULL,

                                                    Last_Name VARCHAR(30) NOT NULL,

                                                        User_ID VARCHAR(10) UNIQUE,

                                                         Salary NUMBER(9,2) DEFAULT 15000);

Q.10    Differentiate between  - 

(i)   DROP TABLE, DROP DATABASE

(ii)  DROP TABLE, DROP clause of ALTER TABLE.

Ans: (i) DROP TABLE will delete a table whereas DROP DATABASE will delete a complete database which will contain different tables. These are DDL commands. Q.11 How will you enlist the name of tables in a database?

Ams: By using SHOW TABLES command. e.g.  mysql>SHOW TABLES;

Q.12    How to view the structures of the table made by you?

Ans: By using DESC <TableName> command. e.g.

          mysql>DESC Employee;

Q.13    How to Drop Table and Databases?

Ans: By using DROP TABLE command. e.g.

          mysql>DROP TABLE Employee;

SIMPLE QUERIES IN SQL || Computer Science || SQL

 

SIMPLE QUERIES IN SQL

Very Short Answer Type Questions  Q.1     Name some basic MySQL SQL elements.

Ans: Some basic MySQL SQL elements are : (i) Literals (ii) Datatypes (iii) Nulls (iv) Comments. Q.2        Differentiate between CHAR and VARCHAR Datatypes.

Ans: The difference between CHAR and VACHAR is that of fixed length and variable length

respectively.

Q.3     Write two usage of DESC in SQL.

Ans: DESC is used with Order by Clause in select statement which displays the data in descending

order. And another user of DESC is that it is used to display the structure of table. 

Q.4       Consider the following Table named „empl‟ and Write SQL commands from (a) to (n)

   

(a) Display all the records from table empl.

Ans: select * from empl;

(b) Display EmpNo and EName of all employees from the table empl.

Ans: select EmpNo, EName from empl;

(c) Display employee name, salary, and department number who are not getting commission from table empl.

Ans: select EName, sal, deptno from empl where comm  = NULL;

(d) Display employee number, name, sal*12 as Annual salary whose commission is not NULL from table empl.

Ans: select EmpNo, EName, sal*12 ‗Annual Salary‘ from empl where sal<>NULL; (e) List all department numbers from table empl.

Ans: select deptno from empl;

(f)  List all unique department numbers from table empl.

Ans: select distinct(deptno) from empl;

(g) List the details of all clerks who have not been assigned department as yet.

Ans: select * from empl where job=‘CLERK‘ and deptno=NULL;

(h) Display the details of employees whose name have only four letters.

Ans: select * from empl where EName like ‗_ _ _ _‘;

(i)  Display the details of all employee whose annual salary is between 25000 to 40000.

Ans: select * from empl where sal between 25000 and 40000; (j) How many job types are offered to employees?

Ans: select count(distinct(job)) from empl;

(k)       List the employees who earn more commission than their salaries.

Ans: select * from emple where sal<comm;

(l)         Display name, job title and salary of employee who do not have manager. Ans: select EName, job, sal from empl where mgr = NULL;

(m)      Display the name of employee whose name contains „A‟ as third letter.

Ans: select EName from empl where EName like ‗_ _ A%‘

(n)       Display the name of employee whose name contains „L‟ as any letter. Ans: select EName from empl where EName like ‗%L%‘

 

Consider the above table „empl‟ and give answer-

Q.5     Write a query to display EName and Sal of employee whose salary is greater than or equal to 2200 from table empl.

Ans: select EName, sal from empl where sal>=2200; 

 

Ans:  (a) Select * from CLUB;

(b)  Select COACHNAME, DATEOFAPP from CLUB order by DATEOFAPP desc; (c)  Select COACHNAME, PAY, AGE, PAY*15/100 ‗BONUS‘ from CLUB;

 

 

 

 

 

 

 

 

 

 

 

 

Ans:  (a) select * from STUDENT1 where Stream = ‗NONMEDICAL‘;

(b)  select Name from STUDENT1 where CLASS like ‗12_‘ order by Stipend;

(c)  select * from STUDENT1 order by AvgMark desc;

(d)  select Name, Stipend, Stream, Stipend*12 ‗Stipend in a Year‘ from STUDENT1;