Scribd
Upload
7 views3 pages

SQL Commands for Student Database Management

Uploaded by

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

SQL Commands for Student Database Management

Uploaded by

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

CHAPTER- RELATIONAL DATABASE & SQL

Q-Consider the tables STUDENT and STREAM given below:


Table: STREAM Table: STUDENT

scode sname rno adno name class section fees scode


S01 Science 1 111 Anu Jain 12 A 2500 S01

S02 Commerce 2 222 Mohit Sharma 11 B 4500 S02


3 333 [Link] 12 B 3000 S02
S03 Humanities
4 444 Ajit Kumar 10 A 2000 S04
S04 General
5 555 Nandini 12 C 3000 S03
6 666 Rohan Sharma 11 B 2500 S02

Write SQl commands for the following:


[Link] a database school.
Create database school;
[Link] the database to school
Use School;
[Link] a table STUDENT & STREAM with the specified structure and constraints:
Table: Stream
Field Datatypes Size Constraint
scode char 3 PRIMARY KEY
sname varchar 15
TABLE:STUDENT
Field Datatype Size Constraint
rno integer 2 PRIMARY KEY
adno integer 3 UNIQUE, NOT NULL
name varchar 20
class integer 2
section char 1
fees float 6, 2
scode char 3 FOREIGN KEY
Create table stream
(scode char(3) PRIMARY KEY,
sname varchar(15));

Create table student


( rno int(2) PRIMARY KEY,
adno int(3) UNIQUE NOT NULL,
name varchar(20),
class int(2),
section Char(1),
fees float(6,2),
scode char(3),
FOREIGN KEY (scode) REFERENCES stream(scode));
[Link] display the structure of table student.
Describe student
[Link] display the structure of table stream.
Describe stream
[Link] records in both the tables.
Insert into stream values(‘S01’,’Science’);
Insert into stream values(‘S02’,’Commerce’);
Insert into stream values(‘S03’,’Humanities’);
Insert into stream values(‘S04’,’General’);
Insert into student values(1,111,’Anu Jain’,12,’A’,2500,’S01’)
Insert into student values(2,222,’Mohit Sharma’,11,’B’,4500,’S02’)
Insert into student values(3,333, ‘[Link]’,12,’B’,3000,’S02’)
Insert into student values(4,444, ‘Ajit Kumar’,10,’A’,2000,’S04’)
Insert into student values(5,555,’Nandini’,12,’C’,3000,’S03’)
Insert into student values(6,666,’Rohan Sharma’,11,’B’, 2500,’S02’)
7. To display the details of all students from the student table.
Select * from student;
8. To display adno, name of all students.
Select adno, name from student;
9. To display the details of all students who are in class 12.
Select * from student where class=12;
10. To display the details of all students who are in not in class 10
Select * from student where class!=10;
11. To display the details of all students who are in class 12 and section is ‘A’.
Select * from student where class=12 and section=’A’
12. To display the details of all students who are in class 11 or 12;
Select * from student where class=11 or class=12;
13. To display the details of all students who are in not in class 11;
Select * from student where not class=11;
14. To display the details of all students whose fees are greater than or equal to 3000 and less than or
equal to 4500.
Select * from student where fees >=3000 and fees<=4500;
15. To display the details of all students who are in class 10 or 11;
Select * from student where class in (10,11);
[Link] display the roll numbers and names of all students who are neither in Class 10 nor in Class 11.
Select rno, name from student where class not in (10,11);
17. To display the names of all students whose fees are between 2000 and 3000.
Select name from student where fees between 2000 and 3000;
[Link] display the names of all students whose fees are not between 2000 and 3000.
Select name from student where fees not between 2000 and 3000;
19. To display the admission number, name, class and annual fees for all students.
Select adno, name, fees*12 from student;
20. To display the admission number, name, and annual fees of all students in class 12 .
Select adno, name, class, fees*12 “Annual Fees” from student where class=12;
[Link] display different types of classes without repetition.
Select distinct class from student;
22. To display the details of all students whose section is not assigned or is null.
Select * from student where section is null;
[Link] display the details of students whose names begin with the character 'A'
Select * from student where name like ‘A%’;
24. To display the details of students whose names ends with the character 'a'
Select * from student where name like ‘%A’;
25. To display the details of students whose names contain the characters 'ar’
Select * from student where name like ‘%ar%’;
[Link] display the roll numbers and names of students whose names contain the letter 'o' in the second
place.
Select rno, name from student where name like ‘_o%’ ;
[Link] display the admission number, name and class of students whose names consist of exactly seven
letters.
Select adno, name, class from student where name like ‘_ _ _ _ _ _ _’
[Link] display the admission number, name and annual fees of all students.
Select adno, name, fees*12 as "Annual Fees" from student;
29. To display student details based on their names in ascending order.
Select * from student order by name;
30. To display the roll numbers, names, and classes of all students in descending order of their class and
ascending order of their names.
Select rno, name, class from student order by class desc, name asc.
31. To count the total number of records in the student table.
Select count(*) from student.
32. To count the total number of values present in the fees column of the student table.
Select count(fees) from student
33. To count the total number of classes in the student table without repetition.
Select count(distinct class) from student;
34. To count the total number of students of class 12 where section = 'B'.
Select count(*) from student where class=12 and section=’B’
[Link] find the sum of the fees of Class 11 students.
Select sum(fees) from student class=11.
36. To find the maximum and minimum fees in the students’ table.
Select max(fees),min(fees) from student;
37. To find the average fees of all students whose section is either A or B.
Select avg(fees) from student where section in(‘A’,’B’);
38. To display the total number of records for each class of students.
Select class, count(*) from student group by class;
39. To display the total number of records for each class of students where the number of records is less
than 3.
Select class, count(*) from student group by class having count(*) <3;
[Link] display the maximum fees, minimum fees, and the sum of fees for each class where the total sum
exceeds 7000.
Select class, max(fees), min(fees), sum(fees) from student group by class having sum(fees)>7000;
41. To display the maximum and minimum fees for each class of 11th and 12th grade students.
Select class, max(fees),min(fees) from student where class in(11,12) group by class;
42. To display rno,name,sname of students from class 12.
Select adno, name, sname from student A, stream B where [Link]=[Link] and class=12;
43. To display scode,name,class,sname of students.
Select [Link],name,sname from student,stream where [Link]=[Link];
44. To display scode,name,class,sname of students whose fees between 2000 and 5000
Select [Link],rno,name,sname from student A,stream B where [Link]=[Link] and
fees between 2000 and 5000;

Common questions

Powered by AI

The `GROUP BY` clause in SQL is used to arrange identical data into groups. This is crucial for deriving aggregate information like sums, averages, or counts related to each group. For example, to find the sum of fees for each class, the query `SELECT class, SUM(fees) from student GROUP BY class;` can be used. This groups the fee information by class and computes the total fees for each class, thus providing a collective financial overview per class .

To calculate the total annual fees for students in class 12 using SQL, you can use the arithmetic operation within the query: `SELECT SUM(fees * 12) AS TotalAnnualFees FROM student WHERE class=12;`. This command multiplies the monthly fees by 12 to get the annual fee for each student and then sums these annual fees to provide the total for all students in class 12 .

Aggregate functions such as `MAX`, `MIN`, and `COUNT` can be combined with `GROUP BY` to categorize data, and `HAVING` to filter groups based on conditions. For example, `SELECT class, MAX(fees), MIN(fees), COUNT(*) FROM student GROUP BY class HAVING COUNT(*) > 2;` will categorize classes, find the maximum and minimum fees within each class, and only return those classes with more than two students. This provides a demographic snapshot, useful for resource planning or identifying trends across different classes .

SQL commands can be crafted using `WHERE` clauses with logical operators to filter records based on multiple conditions. For example, to retrieve students in class 12 and section 'A', you use `SELECT * FROM student WHERE class=12 AND section='A';`. For options involving multiple classes, logical operators `OR` or `IN` can be used, such as `SELECT * FROM student WHERE class=10 OR class=11;` or `SELECT * FROM student WHERE class IN (10,11);`. These constructs allow complex filtering based on different columns .

The `FOREIGN KEY` SQL constraint poses challenges like ensuring that the referenced keys exist, handling cascade updates or deletions, and maintaining referential integrity. If not managed well, it can lead to data inconsistencies or orphan records in the database. Careful design is needed to decide on the actions (`CASCADE`, `RESTRICT`, `SET NULL`) that follow deletions or updates, as these affect how related data is handled, impacting overall database robustness and reliability .

JOIN operations are highly effective in combining related data from multiple tables, providing a unified view of related datasets. In the context of the student and stream tables, a query like `SELECT student.rno, student.name, stream.sname FROM student JOIN stream ON student.scode = stream.scode;` effectively links each student to their respective stream information. This allows comprehensive data retrieval across relational boundaries, facilitating complex queries and analyses, such as reporting and pattern detection .

The foreign key in the STUDENT table ("scode") is related to the primary key in the STREAM table (also "scode"). This relationship enforces referential integrity in the database schema, ensuring that each student's "scode" in the STUDENT table must match an existing "scode" in the STREAM table. This prevents assigning a stream code to a student if that stream does not exist in the STREAM table .

SQL queries can use the `LIKE` keyword with pattern matching to filter information based on specific patterns. For example, `SELECT * FROM student WHERE name LIKE 'A%';` retrieves students whose names start with 'A', `'name LIKE '%a';` finds names ending in 'a', and `LIKE '%ar%';` finds names containing 'ar'. Practical applications include demographic analysis, creating personalized lists, or understanding name distribution patterns for marketing or educational profiling .

Without foreign key constraints between the STUDENT and STREAM tables, database integrity would be compromised. Students could be assigned stream codes that do not exist in the STREAM table, leading to data inconsistencies and orphans within the database. Such an absence of validation could result in incorrect data associations and hinder the reliability and accuracy of queries and analytics performed on the database .

Using the `SELECT DISTINCT` command is significant because it allows retrieval of unique values from a dataset, eliminating duplicates. In the context of class types from the STUDENT table, `SELECT DISTINCT class FROM student;` ensures that each class type is listed only once, aiding in understanding which classes are represented in the data without repetition. This can optimize query results by focusing on unique entries .

You might also like