Table Creation And Data Manipulation Commands Class 11 Computer Science Exam Questions

Please refer to Table Creation And Data Manipulation Commands Class 11 Computer Science Exam Questions provided below. These questions and answers for Class 11 Computer Science have been designed based on the past trend of questions and important topics in your class 11 Computer Science books. You should go through all Class 11 Computer Science Important Questions provided by our teachers which will help you to get more marks in upcoming exams.

Class 11 Computer Science Exam Questions Table Creation And Data Manipulation Commands

Class 11 Computer Science students should read and understand the important questions and answers provided below for Table Creation And Data Manipulation Commands which will help them to understand all important and difficult topics.

Short Answer Type Questions

Question: 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)

Answer: 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,
Chapter – 17: SIMPLE QUERIES IN SQL
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));

Question: What are the different divisions of SQL and commands? Give examples.
Answer: 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.

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

• 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

Answer: 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.
Answer: 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.
Answer: mysql>DESC Employee;

(b) Show the structure of the table job.
Answer: mysql>DESC Job;

(c) Insert 10 records into the table employee as well as Job.
Answer: 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.
Answer: 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.
Answer: mysql>COMMIT

(f) Modify the salary and increase it by 1000, for all who get salary less than 6000.
Answer: mysql>UPDATE Employee SET Salary = Salary+1000 WHERE Salary<6000;

(g) Add one new column in table Employee named „Phone‟.
Answer:mysql>ALTER TABLE Employee ADD(Phone VARCHAR(13));

(h)Provide the phone numbers to all the employees.
Answer: 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.
Answer: mysql>DELETE FROM Employee WHERE First_Name=‘Manish‘;

(j) Remove employee table permanently.
Answer: mysql>DROP TABLE Employee;

(k) Count the total records in the table employee.
Answer:mysql>SELECT Count(*) FROM Employee;

Question: How foreign key command is different from Primary Key command?
Answer: A primary key is a special key in a relational database that acts as a unique identifier for eachrecord meaning it uniquely identifies each row/record in a table and its value should be uniquefor each row of the table. A foreign key, on the other hand, is a field in one table that link twotables together. It refers to a column or a group of columns that uniquely identifies a row ofanother table or same table.
A primary key is a combination of UNIQUE and Not Null constraints so no duplicate values canbe allowed to have in a primary key field in a relational database table. No two rows are allowedto carry duplicate values for a primary key attribute. Unlike a primary key, foreign key cancontain duplicate values and a table in a relational database can contain more than foreign key.

Question: How is Foreign Key commands related to Primary Key?
Answer: A foreign key refers to a field or a collection of fields in a database record that uniquelyidentifies a key field of another database record in some other table. In simple terms, itestablishes a link between records in two different tables in a database. It can be a column in atable that points to the primary key columns meaning a foreign key defined in a table refers tothe primary key of some other table. References are crucial in relational databases to establishlinks 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.

Question: How do you enforce business rules on a database?
Answer: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

Question: What is foreign key? How do you define a foreign key in your table?
Answer: 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));
Ensure the following specification in creIn the above example Emp_id is a foreign key which references the ID field of table Employee.

Question: How to Drop Table and Databases?
Answer: By using DROP TABLE command. e.g.
mysql>DROP TABLE Employee;

Question: What is default value? How do you define it? What is the default value of column for
which no default value is defined?
Answer: 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);

Question: Differentiate between –
(i) DROP TABLE, DROP DATABASE
Prepared By: Sanjeev Bhadauria & Neha Tyagi
(ii) DROP TABLE, DROP clause of ALTER TABLE.
Answer: (i) DROP TABLE will delete a table whereas DROP DATABASE will delete a complete database which will contain different tables. These are DDL commands.

Question: How to view the structures of the table made by you?
Answer: By using DESC <TableName> command. e.g.
mysql>DESC Employee; 

Question: How will you enlist the name of tables in a database?
Answer:  By using SHOW TABLES command. e.g.
mysql>SHOW TABLES;

Question: What are table constraints? What are column constraints? How these two are
different?
Answer: Table constraints apply to groups of one or more columns whereas column constraints applyonly to Individual column.

We hope you liked the above provided Table Creation And Data Manipulation Commands Class 11 Computer Science Exam Questions. If you have any further questions please put them in the comments section below so that our teachers can provide you an answer.