Martin Patel Roll No:-55 DIV-1
ASSIGNMENT-5
1. Create a database named Students_details.db
INPUT:
.open Students_details.db
OUTPUT:
2. Create a table named Students_CS with the following columns and their respective
SQLite data types
INPUT:
create table student_cs(student_id integer primary key,first_name text not
null,last_name text not null, major text DEFAULT 'Computer Science',enrollment_year
Integer,email varchar2(100) null,cgpa real null);
OUTPUT:
3. Insert at least 10 records into Students_CS table.
INPUT:
insert into student_cs(student_id,first_name,last_name,enrollment_year,email,cgpa)
values
...> (1, 'Aarav', 'Sharma', 2022, '[Link]@[Link]', 8.5),
...> (2, 'Ishita', 'Patel', 2021, '[Link]@[Link]', 9.0),
...> (3, 'Rohan', 'Verma', 2023, '[Link]@[Link]', 7.8),
...> (4, 'Khushi', 'Mehta', 2022, '[Link]@[Link]', 8.9),
...> (5, 'Aditya', 'Rao', 2020, '[Link]@[Link]', 7.5),
...> (6, 'Neha', 'Singh', 2023, '[Link]@[Link]', 9.2),
...> (7, 'Vikram', 'Iyer', 2021, '[Link]@[Link]', 8.1),
...> (8, 'Pooja', 'Desai', 2020, '[Link]@[Link]', 8.7),
...> (9, 'Sahil', 'Kapoor', 2022, '[Link]@[Link]', 7.9),
...> (10, 'Ananya', 'Nair', 2021, null, 9.3);
OUTPUT:
Page 1|5
Martin Patel Roll No:-55 DIV-1
4. insert at least 10 records.
INPUT:
select * from student_cs;
OUTPUT:
6. Create a table named Students_Math with the following columns and their respective
SQLite data types
INPUT:
create table student_maths(student_id integer primary key,first_name text not
null,last_name text not null,major text DEFAULT 'Maths',enrollment_year
Integer,email varchar2(100) null,cgpa real null,foreign key(student_id) references
student_maths(student_id));
OUTPUT:
7. Insert at least 10 records.
INPUT:
INSERT INTO
student_maths(student_id,first_name,last_name,enrollment_year,email,cgpa) values
...> (1, 'Aarav', 'Sharma', 2022, '[Link]@[Link]', 8.5),
...> (2, 'Ishita', 'Patel', 2021, '[Link]@[Link]', 9.0),
...> (3, 'Dev', 'Chopra', 2023, '[Link]@[Link]', 8.0),
...> (4, 'Khushi', 'Mehta', 2022, '[Link]@[Link]', 8.9),
...> (5, 'Kavya', 'Rathi', 2020, '[Link]@[Link]', 7.6),
...> (6, 'Neha', 'Singh', 2023, '[Link]@[Link]', 9.2),
...> (7, 'Arnav', 'Bansal', 2021, '[Link]@[Link]', 8.3),
...> (8, 'Pooja', 'Desai', 2020, '[Link]@[Link]', 8.7),
...> (9, 'Tanvi', 'Joshi', 2022, '[Link]@[Link]', 7.8),
Page 2|5
Martin Patel Roll No:-55 DIV-1
...> (10, 'Ananya', 'Nair', 2021, null, 9.3);
OUTPUT:
8. Display all records.
INPUT:
select * from student_maths;
OUTPUT:
9. Display students id, first_name and last_name who are present in both Students_CS
and Students_Math department.
INPUT:
select student_id,first_name,last_name from student_cs
...> intersect
...> select student_id,first_name,last_name from student_maths;
OUTPUT:
Page 3|5
Martin Patel Roll No:-55 DIV-1
10. Display all students id and first_name who are present in both Students_CS and
Students_Math department,including duplicate records if a student present in both
departments.
INPUT:
select student_id,first_name,last_name from student_cs
...> union all
...> select student_id,first_name,last_name from student_maths;
OUTPUT:
11. Display unique student id and first_name who are present in both Students_CS and
Students_Math department.
12. Display student’s details who are present in both Students_CS and Students_Math
13. Find the students who are present in Students_CS but not present in
Students_Math.
14. Find the students who are present in Students_Math but not present in
Students_Math.
15. Display top 2 students from Students_CS with highest cgpa.
16. Display last 5 records from Students_Math.
17. Display only 3 records from Students_CS.
18. Display only 5 to 8 records from Students_Math.
19. Find students in Students_CS who do not have an email address.
Page 4|5
Martin Patel Roll No:-55 DIV-1
20. Find students in Students_Math who do not have an email address.
Page 5|5
