IP Class 11 Tutorial

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.Ans:         Select E.Name, D.DependentName FROM EMPLOYEE E, DEPENDENT DWHERE E.EmpID = D.EmpID;(b) Find employee details working in a department, say, ‘PRODUCTION’.Ans:         SELECT * FROM EMPLOYEE WHERE Department = ‘PRODUCTION’;(c) Find employee […]

Table: STUDENT RollNo Name Stream Section RegistrationID *The values in Stream column can be either Science, Commerce, or Humanities.* The values in Section column can be either I or II.Ans:CREATE TABLE STUDENT (RollNo INT NOT NULL UNIQUE,Name varchar(20) NOT NULL,Stream varchar(20) CHECK (Stream IN (‘Science’, ‘Commerce’, ‘Humanities’)),Section varchar(2) CHECK (Section IN (‘I’, ‘II’)),RegistrationID int PRIMARY

DELETE DROP DELETE is DML statement DROP is DDL statement DELETE is a SQL Command, removes some or all records from a table DROP is a SQL Command, which removes different elements of database like table, constraints or entire database schema. WHERE clause can be used with DELETE WHERE can not be used with DROP

ALTER UPDATE ALTER is a DDL statement UPDATE is a DML statement ALTER is a SQL command that is used to modify, delete or add a column to an existing table in a database. UPDATE is a SQL command that is used to update existing records in a database ALTER command modifies the database schema

(a) Retrieve the MatchID of all those matches where both the teams have scored > 70.Ans:  SELECT MatchID FROM MATCH_DETAILSWHERE FirstTeamScore >70 AND SecondTeamScore >70;(b) Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but SecondTeam has scored > 70.Ans:SELECT MatchID FROM MATCH_DETAILSWHERE FirstTeamScore <70 AND SecondTeamScore >70;(c) Find out the MatchID

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

MovieID MovieName Category ReleaseDate ProductionCost BusinessCost 001 Hindi_Movie Musical 2018-04-23 124500 130000 002 Tamil_Movie Action 2016-05-17 112000 118000 003 English_Movie Horror 2017-08-06 245000 360000 004 Bengali_Movie Adventure 2017-01-04 72000 100000 005 Telugu_Movie Action – 100000 – 006 Punjabo_Movie Comedy – 30500 – (a) Retrieve movies information without mentioning their column names.Ans: SELECT * FROM MOVIE;(b)

(i) What will be the degree of student table?a) 30b) 1c) 3d) 4Ans: 3 (degree means columns and total no of columns are 3) (ii) What does ‘name’ represent in the above code snippet?a) A tableb) A rowc) A columnd) A databaseAns: A column (iii) What is true about the following SQL statement?SelecT * fROM