Scribd
Upload
32 views7 pages

SQL Worksheet for Class XI Informatics

This document is a worksheet for the Informatics Practices subject focusing on SQL theory, containing various questions related to RDBMS, database concepts, SQL queries, and commands. It includes tasks like defining keys, creating tables, writing SQL queries for data retrieval, and differentiating between database concepts. The worksheet is intended for Class XI students and covers a range of SQL topics to enhance their understanding of database management systems.

Uploaded by

moammed
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
32 views7 pages

SQL Worksheet for Class XI Informatics

This document is a worksheet for the Informatics Practices subject focusing on SQL theory, containing various questions related to RDBMS, database concepts, SQL queries, and commands. It includes tasks like defining keys, creating tables, writing SQL queries for data retrieval, and differentiating between database concepts. The worksheet is intended for Class XI students and covers a range of SQL topics to enhance their understanding of database management systems.

Uploaded by

moammed
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

INDIAN SCHOOL DARSAIT

DEPARTMENT OF ICT
Subject: Informatics Practices (065) Topic: SQL (Theory) Worksheet No.: 1
Resource Person: Fathima P. Aliyar Date: 12-05-2019
Name of the Student : Class: XI C Roll Number :

1. What is RDBMS? Name any two RDBMS software.


2. Define the following with suitable examples:
i) Primary Key ii) Alternate Key iii) Candidate Key iv) Foreign Key
3. What is a relation? What is the difference between a tuple and an attribute?
4. Explain relational database model.
5. What are the advantages of DBMS?
6. Define [Link] any two features of DBMS.
7. Differentiate between the following:
i) Primary Key and Candidate Key
ii) CHAR and VARCHAR datatypes
iii) Degree and Cardinality of a relation
8. What do you meant by datatype? Name the various datatypes used in SQL.
9. ‗‗XYZ‘‘ Company conducts workshops for employees of organizations. The company requires data of
workshops that are organized. Write SQL query to create a table ‗Workshop‘ with the following
structure: Field Type Constraint
WorkshopId Integer Primary key
Title Varchar(50)
DateWorkshop Date
NumSpeakers Integer
Insert two rows into the above table.

10. Which keyword is used to eliminate redundant data?


11. Write down SQL statement for creating table LIBRARY having following attributes:
i) BOOK_ID – an integer, primary key of the table
ii) BOOK_TITLE –a string
iii) AUTHOR—a string
iv)QTY – an integer
v) PRICE – a real number
12. Write the SQL query to create the following table:

13. Write the SQL queries based on the following on the following tables:
ISD/XI/IP Worksheet/2019-20 Page 1 of 7
i) Write SQL Query to display ProductName and Price for all products whose Price is in the
range 50 to 150.
ii) Write SQL Query to display details of product whose manufacturer is either XYZ or ABC.
iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are
not given any discount.
iv) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose
city is Delhi.
v) Which column is used as Foreign Key and name the table where it has been used as Foreign key.

14. Create table student with appropriate data types:

Based on above table student answer the following queries:


i) Display names and Roll_No of students whose name starts with ―S‖.
ii) Display records of students having FEE are less than 3000.
iii) Display records whose Fee is from 2500 to 4000.
15. Write command to view the structure of table.
16. Differentiate between DDL and DML statements with suitable examples.
17. Name two open source database software.
18. Find the output of the queries based on the following relation STUDENT:

i) SELECT NAME, GRADE FROM STUDENT WHERE MARKS BETWEEN 40 AND 75;

ii) SELECT * FROM STUDENT WHERE GRADE = ‗A+‘;


19. Differentiate between primary key and alternate key with the help of an example.
ISD/XI/IP Worksheet/2019-20 Page 2 of 7
20. Consider the following relation MobileMaster& MobileStock:

Write the SQL query for questions from (i) to (iv) & write the output of SQL command for questions
from (v) to (vii) given below:
i) Display the Mobile company, name & price in descending order of their Manufacturing date.
ii) List the details of mobile whose name starts with "S" or ends with "a".
iii) Display the Mobile supplier & quantity of all mobiles except "MB003".
iv) Display a list showing the name of mobile company having price between 3000 & 5000.
v) SELECT M_Id, SUM(M_Qty) FROM MobileStock;
vi) SELECT MAX(M_Date), MIN(M_Date) FROM MobileMaster;
vii) SELECT AVG(M_Price) FROM MobileMaster;

21. Consider the following tables FACULTY and COURSES. Write SQL commands for the statements
(i) to (iv) and give outputs for SQL queries (v) to (viii):

ISD/XI/IP Worksheet/2019-20 Page 3 of 7


i) To display details of those faculties whose date of joining is before 31-12-2001.
ii) To display the details of courses whose fees is in the range of 15000 to 50000(both values
included).
iii) To increase the fees of Dream Weaver course by 500.
iv) To display F_ID,FNAME,CNAME of those faculties who charged more than 15000 as fees.
v) SELECT COUNT(DISTINCT F_ID) FROM COURSES ;
vi) SELECT MIN(SALARY) FROM FACULTY;
vii) SELECT SUM(FEES) FROM COURSES ;
viii) SELECT FNAME, LNAME FROM FACULTY WHERE LNAME LIKE ―M%‖ ;
22. From the below table identify the most suitable field for Primary key and candidate key. Also
mention its cardinality and degree:

[Link] NAME STIPEND SUBJECT AVERAGE DIV.


1 KARAN 400 PHYSICS 68 I
2 DIWAKAR 450 COMP. Sc. 68 I
3 DIVYA 300 CHEMISTRY 62 I
23. Write SQL commands for (i) to (vi) and write the output for (vii) on the basis of teacher relation
given below:

(i) To show all information about the teacher of History department.


(ii) To list the name of female teachers who are in Hindi department.
(iii) To list the names of all the teachers with their date of joining in ascendingorder.
(iv) To display Teachers Name, Salary, Age for male teachers only.
(v) To count the number of teachers with age > 23.
(vi) To insert a new row in the Teacher table with the following
data. 9, ―Raja‖, 26, ―Computer‖, {13/05/95}, 2300, "M"
(vii) Give the output of the following SQL statements :
 SELECT COUNT (DISTINCT department) FROM teacher;
 SELECT MAX(age) FROM teacher WHERE sex = "F";
 SELECT AVG(salary) FROM teacher WHERE sex = "M";
 SELECT SUM(salary) FROM teacher WHERE dateofjoin<{12,07/96};

ISD/XI/IP Worksheet/2019-20 Page 4 of 7


24. Write SQL commands for (i) to (iv) and write the outputs for (v) on the basis of table CLUB.

(i) To show all information about the swimming coaches in the club.
(ii) To list name of all coaches with their date of appointment (DATEOFAPP) in
descending order.
(iii) To display a report, showing coachname, pay, age and bonus (15% of pay) for all
the coaches.
(iv) To insert a new row in the CLUB table with the following data :
11, "PRAKASH", 37, "SQUASH", {25/02/98}, 2500, "M"
(v) Give the output of following SQL statements :
 SELECT COUNT (DISTINCT sports) FROM club;
 SELECT MIN (age) FROM club WHERE sex = "F";
 SELECT AVG(pay) FROM club WHERE sports ="KARATE";
 SELECT SUM(pay) FROM club WHERE dateofapp> {31/01/98};
25. Write SQL commands for (i) to (vi) and write the output for (vii) for Lab relations :

(i) To select the Itemname purchased after 31/10/97.


(ii) To list the ItemName, which are within the Warranty period till present date.
(iii) To list the ItemName in ascending order of the date of purchase
(iv) To display ItemName, CostPerltem and Quantity whose Warranty is over.
(v) To count the number of items whose cost is more than 10000.
(vi) To insert a new record in the Lab table.
(vii) Give the output of the following SQL command :
 SELECT MIN (DISTINCT quantity) FROM lab;
 SELECT MIN(warranty) FROM lab WHERE quantity = 2;
 SELECT SUM(costperitem) FROM lab WHERE quantity >2;
 SELECT AVG(csostperitem) FROM lab WHERE dateofpurchase<{1/l/1999};

26. Given the following tables for a database LIBRARY,write SQL commands for (i) to (v) and write the
output for (vi)

ISD/XI/IP Worksheet/2019-20 Page 5 of 7


(i) To show Book name, Author name and Price of books of EPB publishers
(ii) To list the names of books of Fiction Type
(iii) To display the names and price of the books in descending order of their price
(iv) To increase the price of all books of First Publ. by 50
(v) To insert a new row in the table Issued having the following data :―F0002‖, 4
(vi) Give the output of the following queries based on the above tables :
 SELECT COUNT(DISTINCT Publishers) FROM Books;
 SELECT SUM(Price) FROM Books WHERE Quantity >5;
 SELECT Book_Name, Author_Name FROM Books WHERE Price<500;
 SELECT COUNT (*) FROM Books;
27. Consider the following tables. Write SQL commands for the statements (i) to (iv)and give outputs for
SQL queries (v).

ISD/XI/IP Worksheet/2019-20 Page 6 of 7


Write SQL commands for the following statements :
(i) To display details of all the items in the Store table in ascending order of LastBuy.
(ii) To display ItemNo and Item name of those items from Store table Whose Rate is
more than 15 Rupees.
(iii) To display the details of those items whose Suppliers code (Scode) is 22 or Quantity
in Store (Qty) is more than 110 from the table Store.
(iv) To display Minimum Rate of items for each Supplier individually as per Scode from
the table Store.
(v) Give the output of the following SQL queries:
 SELECT COUNT (DISTINCT Scode) FROM Store;
 SELECT Rate*Qty FROM Store WHERE ItemNO = 2004;
 SELECT Item, Sname FROM Store Item No = 2006 ;
 SELECT MAX (LastBuy) FROM Store ;
28. Consider the following tables SCHOOL and ADMIN. Write SQL commands for the statements (i) to(iv)
and give outputs for SQL queries (v) to (vii).

(i) To display TEACHERNAME, PERIODS of all teachers whose periods less than 25.
(ii) To display TEACHERNAME, CODE and DESIGNATION from tables SCHOOL
and ADMIN whose gender is male.
(iii) To display number of teachers in each subject wise.
(iv) To display CODE, TEACHERNAME and SUBJECT of all teachers who have joined the
school after 01/01/1999.
(v) SELECT MAX (EXPERIENCE) FROM SCHOOL;
(vi) SELECT TEACHERNAME, SUBJECT FROM SCHOOL WHERE DESIGNATION=
‗COORDINATOR‘;
(vii) SELECT COUNT (DISTINCT SUBJECT) FROM SCHOOL;

ISD/XI/IP Worksheet/2019-20 Page 7 of 7

Common questions

Powered by AI

Optimizing SQL queries for large datasets involves strategic indexing and restructuring queries for efficiency. Indexing critical columns—especially those frequently used in WHERE clauses—speeds up data retrieval by allowing the database to locate data more efficiently. Composite indexes can be beneficial for columns frequently queried together. Query structure improvements include minimizing subqueries, using JOINs instead of subqueries when possible, and ensuring WHERE clauses use indexed columns. Additionally, avoiding SELECT * to only retrieve necessary columns reduces the data load. Use of SQL functions should be minimized on indexed columns as they limit index use. These optimizations enhance performance by reducing CPU time and memory usage .

In SQL, CHAR is a fixed-length data type, meaning all values stored as CHAR take up the full defined space regardless of actual string length. VARCHAR is a variable-length data type that allocates only as much space as needed for the string plus one byte for its length. This difference impacts performance and storage: CHAR is faster for retrieval as it offers predictable space allocation but can lead to wasted storage if values are significantly shorter than the maximum length. VARCHAR is more storage-efficient as it adapts to the actual content size but can be slightly slower on retrieval due to variable space allocation. The choice between them depends on data consistency and space-efficiency requirements .

SQL constraints are vital for enforcing rules that end-users might otherwise overlook, enhancing database accuracy and reliability. Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK define limits on data types to prevent invalid data entry. For example, a PRIMARY KEY constraint ensures each record is unique and FOREIGN KEY constraints enforce relationships between tables for referential integrity. UNIQUE ensures field values are distinct, and NOT NULL ensures a field must always contain a value. CHECK constraints enforce specific conditions on data before entry, such as ensuring age is greater than zero. These tools ensure data integrity, prevent anomalies, and uphold the logical structure of the database .

Normalization helps minimize data redundancy and enhance data integrity by logically organizing tables to ensure dependencies are properly enforced through a series of normal forms, each removing specific types of redundancy and dependencies. For instance, First Normal Form (1NF) eliminates duplicate columns, ensuring atomic values. Second Normal Form (2NF) removes partial dependencies, requiring all non-key attributes to be fully functionally dependent on the primary key. Third Normal Form (3NF) removes transitive dependencies, ensuring non-key attributes are independent of each other. By structuring data this way, normalization minimizes redundancy and scales up data integrity, preventing anomalies during insertions, updates, and deletions .

A relational database model organizes data into related tables or relations, representing real-world entities and relationships through rows (tuples) and columns (attributes). Each table contains a primary key to uniquely identify rows and may contain foreign keys to establish links with other tables, reflecting complex interrelationships. Its importance lies in efficiently managing and retrieving structured data, supporting complex querying and integrity constraints. Real-world applications include customer relationship management (CRM) systems, which store customer data and their interactions across various channels, and enterprise resource planning (ERP) systems that integrate financial, human resource, and supply chain data across business units .

A DBMS offers numerous advantages over traditional file systems, including data abstraction, minimal redundancy, data integrity, security, and facilitation of complex queries. DBMSs abstract the data storage details, allowing developers to manipulate data without knowing physical storage details, leading to easier application development and maintenance. They also minimize data redundancy through normalization, ensuring data is updated in one place only. Furthermore, DBMSs enforce data integrity constraints and security protocols to protect against unauthorized access. These features facilitate better data management by ensuring reliability, consistency, and security, which are often challenging to achieve in traditional file systems where data redundancy, integrity, and security require considerable manual configuration and oversight .

Foreign keys maintain referential integrity by ensuring that a relationship between two tables remains consistent, typically linking a foreign key in one table to a primary key in another. This constraint prevents orphaned records and ensures data remains reliable and interconnected, crucial for accurate join operations, data retrieval, and reporting. If foreign keys are not enforced, it can lead to data anomalies like unintended data loss, as deletions in a referenced table might leave invalid references in related tables, leading to erroneous data queries and reports. This undermines the logical relationships that a relational database aims to maintain .

A primary key is a unique identifier for a row in a table, ensuring that each record is distinct and there cannot be two rows with the same primary key value. An alternate key, however, is any candidate key that is not chosen as the primary key; it can also uniquely identify a row but is not used as the primary key. The main difference lies in their use: the primary key is used as the main reference point for ensuring data integrity, such as foreign key references, while alternate keys act as backup identifiers. For example, in a table of employees, 'EmployeeID' could be the primary key while 'Email' could serve as an alternate key. This distinction ensures data integrity by maintaining uniqueness across fields used for critical database operations .

In a relational database, the 'degree' of a relation refers to the number of attributes (columns) in a table, while 'cardinality' refers to the number of tuples (rows) in a table. Degree provides a structural insight into the complexity and breadth of information that can be stored in a table. Cardinality, on the other hand, reveals the volume of data managed. Understanding both is crucial in database design as they affect storage, indexing, performance, and scalability. A higher degree might complicate queries and schema, while high cardinality can impact performance and require robust indexing strategies .

DDL (Data Definition Language) and DML (Data Manipulation Language) serve different purposes in SQL operations. DDL is used to define, alter, and manage the schema structure of the database, including operations like CREATE, ALTER, and DROP. For instance, CREATE TABLE creates a new table, while ALTER TABLE modifies an existing one. DML, conversely, deals with data manipulations within existing schema, including commands like SELECT, INSERT, UPDATE, and DELETE. An example would be INSERT INTO adding new records to a table. While DDL impacts the schema and structural components, DML strictly controls data entry, modification, and retrieval within those structures .

You might also like