Student Management System is purely developed by using DBMS queries. The main functions involved to develop this project is developing ER diagram, DDL/DML, SQL constraints, Retrieving data using the select function, Restrictions, and sorting functions, Aggregate, and grouping, Single row function, Joins, and finally Subquery.
ER DIAGRAM OF STUDENT MANAGEMENT SYSTEM
DDL AND DML
1) Create a table name student and attribute of s_id, first last and middle name, address, email, city, and state.
2) Display the datatype of all attributes in the student table?
3) Insert values into the table student?
4) Change the datatype of s_id in the student table?
5) Update the city of s_id=001 to bby?
SQL CONSTRAINTS
1) Create a table name student with an attribute s_id that is not null?
2) Create a table name student with an attribute s_id that is unique?
3) Create a table name student with an attribute s_id which is a primary key?
4) Create a table name teacher with an attribute s_id which is a foreign key and team name?
5) Create a table name student and with an attributes city which a default constraints “KOLKATA”?
RETRIEVING DATA USING SELECT
1) Display all the columns of the table name course?
2) Display the columns c_name aliases course name of table name course?
3) Display the columns c_fee and c_name using concatenation and aliases course details of table name course?
4) Display the course fee and increase in course fee by 3000 where the course duration is 45_hr?
5) Remove the duplicate value from course duration with a student ID from the table name course?
RESTRICTIONS AND SORTING
1) Display the student ID and the first name from the table name student whose state is westbengal?
2) Display all the data of the table course whose course ID is ‘11’.
3) Display all the data of the table course whose course fee range is less than 100000?
4) Display the Lastname and city of the table student where the student’s first name starts with ‘a’ and consists of 4 characters?
5) Display the course fee of the table name course which is between 10000 to 50000?
AGGREGATE AND GROUPING
1) Display the average course fee for all the courses in the given table?
2) Display the minimum and maximum course fees in the given table?
3) Display the count number of course names from the table name course?
4) Display the total course fee in the table name course?
5) Display the student ID and minimum course fee from the course table and group by student ID having a course fee less than 102000 and sort the minimum course fee in descending order?
SINGLE ROW FUNCTION
1) Display the student ID and student first name and state from the student table where convert student first name and state into uppercase?
2) Display the student ID and student first name and state from the student table where convert student first name and state into lowercase?
3) Display the student ID and student state from table student and change the first character of all states to uppercase?
4) Display the student ID and student first name middle name last name together from the table student use the character-manipulation function?
5) Display the student ID and the length of the first name and the last name from the table student using the character-manipulation function.
JOINS
1) Display the registration number for the user’s table and the student’s first name on the table student use inner join?
2) Display the registration number for the user’s table and the student’s first name on the table student use left join and order by the first name.
3) Display the registration number for the user’s table and the student’s first name and last name from the table student use the right join and order by registration number.
4) Select all teachers and all courses in the given table using full join and order by teacher ID.
5) Select all students’ first names and last names and city in the given table using self-join and order by the city?
SUBQUERY
1) Display the first middle and last name of the student where the course fee is minimum?
2) Display the teacher ID and first middle and last name of the teacher where the course name is DBMS?
3) Display the phone number of the student of who’s registration number is 20?
4) Display the registration number who live in Jaipur?
5) Display teacher ID first name and phone number whose qualification is b.tech?