Class-12 CS Chapter-13 Assignment Solution
CHAPTER-13
CLASS-XII COMPUTER SCIENCE ASSIGNMENT SOLUTIONS
Conceptual Questions
Q.1 What are different divisions of SQL and commands? Give examples of commands in each division.
Ans: Division of SQL commands are:
- Data Definition Language (DDL) -DDL SQL commands are used to defining, redefining and modifying, and dropping various database objects. Types of DDL commands:
- ALTER TABLE command -This command is used in Adding column, Modifying column definition, Removing table component.
Example: ALTER TABLE <table_name> ADD <column_name>;
- DROP TABLE command – this command is used to drop the table.
Example:DROP TABLE <table_name>
- Data Manipulation Language (DML)– DML SQL commands are dedicated to manipulate data in one or another way. Types of DML command
- INSERT Command -This command is used in inserting NULL values, inserting dates, inserting data from another table.
Example: INSERT INTO <table_name> [<COLUMN LIST>] VALUES( <VALUE>,<VALUE>....);
- UPDATE command– This command is used in Modifying data as below :
Example: UPDATE <table_name> SET [<COLUMN_NAME>] = <VALUE>
- DELETE command -This command is used to Delete data as below:
Example: DELETE FROM <table_name>[WHERE <predicate>];
Q.2. 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.
- A FOREIGN KEY is a key used to link two tables together.
- A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
- FOREIGN KEY(<column name>) REFERENCES <parent table name> (<Column name>)
Q.3 How is FOREIGN KEY commands different from PRIMARY KEY command?
Ans: A FOREIGN key is a column, or combination of columns, that identifies the referential connection between the data in two databases. The data in the given column are matched with the data in the referred table using a foreign key as a referential connection. The table that contains a remote key is referred to as the child table, while the referenced table is known as the parent table.
FOREIGN KEY commands different from PRIMARY KEY command in following ways: –
|
Primary Key |
Foreign Key |
| It is used to uniquely identify data | Used to established relation between two tables |
| Combination of unique and not null | Can contain duplicate value in table |
| It does not allow null value | It allow null value |
| Ensures data in specific column is unique | Foreign key references to primary key of another table |
Q.4 How is FOREIGN KEY commands related to the Primary Key?
Ans: A relational database’s term for a one-to-many relationship between two tables is primary key-foreign key relationship. A column or group of columns in one table that relate to the primary key columns in another table is known as a foreign key. The term “primary key” refers to a column (or combination of columns) where each value uniquely identifies a specific row of the table.
Q.5 How do you enforce business rules on a database?
Ans: To enforce business rules on a database use following constraint: –
- Referential Integrity
- Unique Constraint
- Primary key Constraint –
- A Foreign Key
Q.6 What are table constraints? What are column constraints? How are these two different?
Ans: A Constraint is a condition or check applicable on field or set of fields.
The two basic type of constraints are Column constraints and table constraints. The difference between the two is that column constraints apply only to individual columns, whereas table constraints apply to group of one or more columns.
Q.7. What is default value? How do you define it? What is the default value of column for which no default value is define.
Ans: Default worth is employed once the user doesn’t specify any worth. Default worth is outlined because of the worth that’s such as for a column exploitation DEFAULT Clause. By default, a table column will hold NULL values.
Q.8. Differentiate between:
(i) DROP TABLE, DROP DATABASE (ii) DROP TABLE, DROP clause of ALTER TABLE
Ans: (i) DROP table = it deletes table or relation from database
DROP database= it deletes the database from MYSQL server.
(ii) DROP table = it deletes table or relation from database.
DROP clause of ALTER TABLE = it deletes a particular column or filed from table or relation.
TYPE B: Application Based Questions
Q.1. Insert all those records of table Accounts into table Pending where amt_outstanding is more than 10000.
Ans: INSERT INTO Pending SELECT * FROM Accounts WHERE amt_outstanding>10000;
Query if you have not created Pending table in your database: –
CREATE TABLE Pending SELECT * FROM Accounts WHERE amt_outstanding>10000;
Q.2. Increase salary of employee records by 10% (table employee).
Ans: UPDATE employee SET salary = salary + salary * 0.10;
Q.3. Give commission of ₹ 500 to all employees who joined in year 1982 (table employee)
Ans: UPDATE Empl SET comm = comm + 500 WHER hiredate BETWEEN “1982-01-01” AND “1982-12-31”;
Q.4. Allocate the department situated in BOSTON to employee with employee number 7500 (tables empl, Dept)
Ans: ALTER TABLE Empl ADD (city char(50));
UPDATE Empl SET city = “BOSTON” WHERE empno = 7500;
Q.5. Given the following tables:
- Orders (OrdNo, Ord_date, ProdNo, Qty)
- Product (ProdNo, Descp, Price)
- Payment (OrdNo,Pment)
Write a query to delete all those records from table Orders whose complete payment has been made.
Ans:
DELETE FROM Orders WHERE Orders.OrdNo = Payment.OrdNo AND Orders.ProdNo = Product.ProdNo AND Payment.Pment = Orders.Qty * Product.Price;
Explaination: – I must first create a condition that links the order, payment, and product tables before creating a second condition to determine whether the customer’s payment is equal to the product price multiplied by the quantity.
Q.6. Enlist the names of all tables created by you.
Ans: SHOW TABLES;
Q.7. Write Query statements for following transaction: (Consider tables of question 12)
- Increase price of all products by 10%
- List the details of all orders whose payment is pending as per increased price.
- Decrease prices by 10% for all those products for which orders were placed 10 months before.
Ans:
(i) UPDATE product SET price = price + price * 0.10;
(ii) SELECT * FROM orders, product, payment WHERE orders.OrdNo = Payment.OrdNo AND ProdNo# = ProdNo AND (Qty*Price) < Pment;
(iii) UPDATE Product SET Price=Price - Price*0.1 WHERE Product.ProdNo=Orders.ProdNo AND DATEDIFF(month, Orders.Ord_date, GETDATE())>=10;
Q.8. Modify table Empl, add another column called Grade of Varchar datatype, size 1 into it.
Ans: ALTER TABLE Empl ADD COLUMN grade varchar(1);
Q.9. In the added Column Grade, assign grades as follows: –
- If sal is in range 700-1500, Grade is 1; if sal is in range 1500-2200, Grade is 2
- If sal is in range 2200-3000, Grade is 3; if sal is in range 300- Grade is 4.
Ans:
UPDATE Empl SET grade = 1 WHERE sal BETWEEN 700 AND 1500;
UPDATE Empl SET grade = 2 WHERE sal BETWEEN 1500 AND 2200;
UPDATE Empl SET grade = 3 WHERE sal BETWEEN 2200 AND 3000;
UPDATE Empl SET grade = 4 WHERE sal = 3000;
Q.10. Add a constraint (NN-Grade) in table Empl that declares column Grade not null.
Ans: ALTER TABLE Empl ADD COLUMN grade int(1) NOT NULL;
Q.11 Insert a record of your choice in table Empl. Make sure not to enter Grade.
Ans: INSERT INTO Empl(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(8935,“Dominic”, “SALESMAN”,8862 ,“1993-06-25”,1000,110.00,10)
NOTE:- Because we did not enter a grade, it will return an error. Grade should have a value.
Q.12 Modify the definition of Column Grade. Increase its size to 2.
Ans: ALTER TABLE Emple MODIFY COLUMN grade int(2) NOT NULL;
Q.13. Drop the table empl.
Ans: DROP TABLE empl;
Q.14. Create the table Department table based on the following table instance chart.
Ans: CREATE TABLE department (id int(8), name varchar (25));
Q.15. Populate the table Department with data from table dept. Including only required columns.
Ans: INSERT INTO Department () SELECT id, name FROM dept;
Q.16. Create the table Employee based on the following table instance chart.
Ans:
CREATE TABLE employee ( Id int(8), First_name varchar(25), Last_name varchar(25), Dept_id int(8) );
Q.17. Drop table Employee and Department.
Ans: DROP TABLE Employee;
DROP TABLE Department;
Q.18. Create table Customer as per following Table Instance Chart.
Ans:
CREATE TABLE customer ( Cust_ID int (7), Cust_Name varchar(30), Cust_Address1 varchar (20), Cust_Address2 varchar (30), Pincode int(6), Cust_Phone varchar(10) );
Q.19. Add one column Email of data type VARCHAR and size 30 to the table Customer.
Ans: ALTER TABLE Customer ADD (Email varchar(30));
Q.20 Add one more column CustomerIncomeGroup of datatype VARCHAR(10)
Ans: ALTER TABLE Customer ADD (CustomerincomeGroup varchar(10));
Q.21 Insert few records with relevant information, in the table.
Ans: INSERT INTO Customer VALUES (1234,“Awmtea”,“India”,“up”,211001,1234567890);
INSERT INTO Customer VALUES (1597,“Portal express”,“India”,“hp”,245901,2468135790);
Q.22 Drop the column CustomerIncomeGroup from table Customer.
Ans: ALTER TABLE Customer DROP COLUMN CustomerlncomeGroup;
Q.23. Create table Department as per following Table Instance Chart.
Ans:
CREATE TABLE IF NOT EXISTS department ( DeptID int(2) PRIMARY KEY, DeptName varchar(20) NOT NULL );
Q.24. Create Table Employee as per following table Instance Chart.
Ans:
CREATE TABLE IF NOT EXISTS Employee ( EmpID int(6) PRIMARY KEY, EmpName Varchar(20) Not null, EmpAddress varchar (20), EmpPhone Varchar(10), EmpSal int(9), DeptID Varchar(2), FOREIGN KEY (DeptID) REFERENCES Department (Dept_ID) );
Q.25. View structures of all tables created by you.
Ans: DESC Employee;
DESC Department;
DESC customer;
