Chapter 8: Introduction To Structured Query Language (SQL)

NCERT solutions of  Introduction To Structured Query Language (SQL)

1. Match the following clauses with their respective functions.

1.ALTER	i) Insert the values in a table
2.UPDATE	ii) Restrictions on columns
3.DELETE	iii) Table definition
4.INSERT INTO	iv) Change the name of a column
5.CONSTRAINTS	v) Update existing information in a table
6.DESC	vi) Delete an existing row from a table
7.CREATE	vii) Create a daatabase

1 – iv 2 – v 3 – vi 4 – i 5 – ii 6 – iii 7 – vii

2. Choose appropriate answer with respect to the following code snippet. CREATE TABLE student ( name CHAR(30), student_id INT, gender CHAR(1), PRIMARY KEY (student_id) ); a) What will be the degree of student table? i) 30 ii) 1 iii) 3 iv) 4

Ans. iii) 3

b) What does ‘name’ represent in the above code snippet? i) a table ii) a row iii) a column iv) a database

Ans. iii) a column

c) What is true about the following SQL statement? SelecT * fROM student; i) Displays contents of table ‘student’ ii) Displays column names and contents of table ‘student’ iii) Results in error as improper case has been used iv) Displays only the column names of table ‘student’

Ans.: ii) Displays column names and contents of table ‘student’

d) What will be the output of following query? INSERT INTO student VALUES (“Suhana”,109,’F’), VALUES (“Rivaan”,102,’M’), VALUES (“Atharv”,103,’M’), VALUES (“Rishika”,105,’F’), VALUES (“Garvit”,104,’M’), VALUES (“Shaurya”,109,’M’); i) Error ii) No Error iii) Depends on compiler iv) Successful completion of the query

Ans.: i) Error

e) In the following query how many rows will be deleted? DELETE student WHERE student_id=109; i) 1 row ii) All the rows where student ID is equal to 109 iii) No row will be deleted iv) 2 rows

Ans.: ii) All the rows where student ID is equal to 109

3. Fill in the blanks: a) ___________ declares that an index in one table is related to that in another table. i) Primary Key ii) Foreign Key iii) Composite Key iv) Secondary Key

Ans.: Primary Key

b) The symbol Asterisk (*) in a select query retrieves __. i) All data from the table ii) Data of primary key only iii) NULL data iv) None of the mentioned

Ans. i) All data from the table

4. Consider the following MOVIE database and answer the SQL queries based on it. a) Retrieve movies information without mentioning their column names.

select * from movie;

b) List business done by the movies showing only MovieID, MovieName and BusinessCost.

select movieid,moviename,businesscost from movie where businesscost is not null;

c) List the different categories of movies.

select distinict(category) from movie;

d) Find the net profit of each movie showing its ID, Name and Net Profit. (Hint: Net Profit = BusinessCost – ProductionCost) Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name is coined for such columns? What can you say about the profit of a movie which has not yet released? Does your query result show profit as zero?

select movieid, moviename, businesscost-productioncost as ‘NetProfit’ from movie;

e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.

select movieid,moviename,productioncost from movie where productioncost>80000 and productioncost<125000; or select * from movie where productioncost between 80000 and 125000;

f) List all movies which fall in the category of Comedy or Action.

select * from movie where category=’Comedy’ or category=’Action’ ; OR select * from movie where category in (‘Comedy’,’Action’);

g) List the movies which have not been released yet.

select * from movide where releasedate is null; NCERT solutions MySQL

5. Suppose your school management has decided to conduct cricket matches between students of class XI and Class XII. Students of each class are asked to join any one of the four teams — Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following: a) Create a database “Sports”.

create database sports; use sports;

b) Create a table “TEAM” with following considerations: i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team. ii) Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

create table team (teamid int(1), teamname varchar(10));

c) Using table level constraint, make TeamID as primary key.

alter table team add primary key (teamid);

d) Show the structure of the table TEAM using SQL command.

desc team;

e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table: Row 1: (1, Team Titan) Row 2: (2, Team Rockers) Row 3: (3, Team Magnet) Row 4: (4, Team Hurricane)

insert into team values (1,’Team Titan’); insert into team values (2,’Team Rockers’); insert into team values (3,’Team Magnet’); insert into team values (4,’Team Hurricane’);

f) Show the contents of the table TEAM.

select * from team;

g) Now create another table below. MATCH_DETAILS and insert data as shown in the table. Choose appropriate domains and constraints for each attribute.

create table MATCH_DETAILS (matchid char(2) primary key, matchdate date, firstteamid int(1) references team, secondteamid int(1) references team, firstteamscore int(2), secondteamscore int(2)); insert into match_details values (‘M1′,’2018-07-17’,1,2,90,86), (‘M2′,’2018-07-18’,3,4,45,48), (‘M3′,’2018-07-19’,1,3,78,56), (‘M4′,’2018-07-19’,2,4,56,67), (‘M5′,’2018-07-20’,1,4,32,87), (‘M6′,’2018-07-21’,2,3,67,51);

6. Using the sports database containing two relations (TEAM, MATCH_DETAILS), answer the following relational algebra queries. a) Retrieve the MatchID of all those matches where both the teams have scored > 70.

select matchid from match_details where firstteamscore >70 and secondteamscore>70;

b) Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but SecondTeam has scored > 70.

select matchid from match_details where firstteamscore<70 and secondteamscore>70;

c) Find out the MatchID and date of matches played by Team 1 and won by it.

select matchid, matchdate from match_details where firstteamid=1 and firstteamscore>secondteamscore;

d) Find out the MatchID of matches played by Team 2 and not won by it.

select matchid, matchdate from match_details where firstteamid=2 and firstteamscore<secondteamscore;

e) In the TEAM relation, change the name of the relation to T_DATA. Also, change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

rename table team to t_data; Way 1 alter table t_data rename column teamid to t_id; alter table t_data rename column teamname to t_name; Way 2 alter table t_data change teamid t_id int(1); alter table t_data change teamname t_name varchar(20);

7 Differentiate between the following commands: a) ALTER and UPDATE

Alter This command is used to modify the table structure. This is a DDL command. It will add, modify or drop any column or constraints. Example: alter table emp add column remarks varchar(20); Update This command is used to modify the table contents. This is a DML command. It will set new values to the already exists values in a table. Example: update emp set sal=5000 where empno=1256;

b) DELETE and DROP

Delete Delete is used to remove the rows from the table. Delete is the DML command. Example: delete from emp where empno=1245; Drop Drop is used to deleting a table. Drop is DDL command. Example: drop table emp;

NCERT solutions MySQL 8. Create a database called STUDENT_PROJECT having the following tables. Choose appropriate data type and apply the necessary constraints.

create database stduent_project; use student_project; create table project (project_id varchar(10) primary key, projectname varchar(20) not null, submissiondate date, teamsize int(2), guideteacher varchar(20)); create table project_assigned (registration_id varchar(10) primary key, project_id varchar(10) references project, assigndate date); create table student (rollno int(4) primary key, name varchar(20), stream varchar(15) not null check(stream in(‘Science’,’Commerce’,’Humanities’)), section char(2) not null check(section in(‘I’,’II’)), registration_id varchar(10) references project_assigned);

a) Populate these tables with appropriate data.

insert into project values(1,’Library Management’,’2020-12-15′,3,’Sanjay Sir’); insert into project_assigned values(‘XI-A012021′,1,’2020-04-08’); insert into student values(1101,’Palak Shah’,’Science’,’I’,’XI-A012021′);

b) Write SQL queries for the following: c) Find the names of students in Science Stream.

select name from student where stream=’science’;

d) What will be the primary keys of the three tables?

Ans.: The following are the primary keys for three tables: Table Primary Key Project project_id project_assigned regsitrations_id student rollno

e) What are the foreign keys of the three relations?

Ans.: The following are the foreign keys for three relations: Relation Foreign Key project_assigned project_id student registration_id

f) Find names of all the students studying in class ‘Commerce stream’ and are guided by the same teacher, even if they are assigned different projects.

select student.name,project.guideteacher from student, project where student.stream=’Commerce’ and project.guideteacher=’Sanjay Sir’

NCERT solutions MySQL 9.An organization ABC maintains a database EMPDEPENDENT to record the following details about its employees and their dependents. EMPLOYEE(AadhaarNo, Name, Address, Department,EmpID) DEPENDENT(EmpID, DependentName, Relationship) Use the EMP-DEPENDENT database to answer the following SQL queries: a) Find the names of employees with their dependent names.

select employee.name,dependent.name from employee,dependent;

b) Find employee details working in a department, say, ‘PRODUCTION’.

select * from employee where department=’Production’;

c) Find employee names having no dependent.

select employee.name from employee, dependent where employee.empid is null;

d) Find names of employees working in a department, say, ‘SALES’ and having exactly two dependents.

select employee.name from employee,dependent where employee.department=’sales’ and count(dependent.empid)=2;

A shop called Wonderful Garments that sells school uniforms maintain a database SCHOOL_UNIFORM as shown below. It consisted of two relations — UNIFORM and PRICE. They made UniformCode as the primary key for UNIFORM relation. Further, they used UniformCode and Size as composite keys for PRICE relation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies. a) The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL query to change the name of the relation PRICE to COST.

rename table price to cost;

b) M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of `100 each. Insert this record in COST table

Its not possible to insert records into cost table. If you want to do that you must insert record in the uniform table.

c) When you used the above query to insert data, you were able to enter the values for handkerchief without entering its details in the UNIFORM relation. Make a provision so that the data can be entered in COST table only if it is already there in UNIFROM table.

insert into uniform values(7,’Handkerchiefs’,’Red’); insert into cost (UCode,Price) values(7,100);

d) Further, you should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add an appropriate constraint to the SCHOOL_UNIFORM database.

alter table uniform add constraint check (UName is not null) NCERT Class 11 Solutions can be of great value if you are trying to excel in your school examinations. The journey starts directly from when you step into class 1 as every class holds great to use as you progress. CBSE Class 11 is no doubt a very valuable class and the last class of your school life. Not to mention, it also leads to a lot of career-making decisions as you seem for some important competitive exams to get your desire college. NCERT solution can help you immensely throughout this adventure of yours. NCERT Class 11 Solutions Although higher classes students like Class 10-12 should have a better knowledge of all the topics that will help them to examine deeper into the basics as well as an advanced level of questions that are asked in many competitive exams after 12 class. One of the best ways to achieve it is by answering questions of NCERT Books with the help of NCERT solutions. We at SelfStudys strive hard to devise better ideas in order to help students and give you detailed solutions for all questions from the NCERT textbook as per the latest CBSE CCE marking scheme pattern is to back you up in useful learning in all the exams conducted by CBSE curriculum. The plethora of advantages of referring to NCERT solutions requires an in-depth understanding of detailed solutions of all NCERT book’s questions. We have given all the detailed NCERT questions and solutions in PDF format which you can read for FREE. All you have to do is download the SelfStudys NCERT Solutions for Class 11 once for FREE and you are able to go. You can get all the Subject NCERT solutions by the links provided here. NCERT Solutions for all the subjects of Class 11 is available on this page including NCERT Class 11 Solution for Maths, Chemistry, Physics, English, Business Studies, Biology, economics are provided below to download in a free PDF file. NCERT Solution for Class 11 NCERT Class 11 Solutions to the questions with Chapter-wise, detailed are given with the objective of helping students compare their answers with the example. NCERT books provide a strong foundation for every chapter. They ensure a smooth and easy knowledge of advanced concepts. As suggested by the HRD ministry, they will perform a major role in JEE. NCERT Class 11 Solutions | NCERT Solution for Class 11 NCERT Solution for Class 11 includes all the questions given in NCERT Books for all Subjects. Here all questions are solved with detailed information and available for free to check. NCERT Solutions for Class 11 are given here for all chapter-wise. Select the subject and choose a chapter to view NCERT Solution chapter-wise. We hope that our NCERT Class 11 Solutions helped with your studies! If you liked our NCERT Solutions for Class 11, please share this post. Why NCERT Solutions for Class 11 is Important? NCERT Class 11 Solutions increases the base for conceptual knowledge. It provides a complete view of the prescribed syllabus, as the textbook doesn't have a detailed description of the syllabus nor solutions. All the important theorems and formulas of Class 11 are completely solved, which works to make the concepts and find new links to them. Importantly, the NCERT Class 11 Solutions is the point of reference for those students planning for competitive examinations such as NRRT, JEE Main/Advanced, BITSAT & VITEEE, etc. When the NCERT book is accompanied by the solutions, the knowledge of concepts becomes simple and in-depth. Moreover, students can trail into the solutions without break in topics, as it is designed to give a step-by-step explanation. Try out the Class 11 Solutions and learn from the resources. Why try NCERT Class 11 Solutions? Well, here are the solid reasons why you should and must try out the NCERT Class 11 Solutions. Has ERRORLESS answers with 100% Reasoning The quality of solutions is perfect Gives in-depth knowledge with notes shortly after the solutions with shortcuts, tips, alternative methods & points to remember Gives quick revision of the concepts included along with important definitions and Formulas on the chapters, which acts as a prepared refresher. Designed step-by-step to give 100% Concept Clearness