Consider following schema and write SQL for given statements.Student (RollNo, Name, DeptCode, City), Department (DeptCode, DeptName), Result (RollNo, Semester, SPI)Create tables with Primary Key, foreign key constraints in given schemas.1)Retrieve all student names and their respective department names.2)Retrieve the average SPI (Semester Performance Index) for each student.3)Retrieve the students who have the highest SPI semester -wise.4)Retrieve the students who belong to a Ahmedabad city with their department names
Question
Consider following schema and write SQL for given statements.Student (RollNo, Name, DeptCode, City), Department (DeptCode, DeptName), Result (RollNo, Semester, SPI)Create tables with Primary Key, foreign key constraints in given schemas.1)Retrieve all student names and their respective department names.2)Retrieve the average SPI (Semester Performance Index) for each student.3)Retrieve the students who have the highest SPI semester -wise.4)Retrieve the students who belong to a Ahmedabad city with their department names
Solution
Sure, here are the SQL queries for the given statements:
- To create tables with Primary Key, foreign key constraints in given schemas:
CREATE TABLE Department (
DeptCode INT PRIMARY KEY,
DeptName VARCHAR(255)
);
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(255),
DeptCode INT,
City VARCHAR(255),
FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode)
);
CREATE TABLE Result (
RollNo INT,
Semester INT,
SPI FLOAT,
PRIMARY KEY (RollNo, Semester),
FOREIGN KEY (RollNo) REFERENCES Student(RollNo)
);
- To retrieve all student names and their respective department names:
SELECT Student.Name, Department.DeptName
FROM Student
INNER JOIN Department ON Student.DeptCode = Department.DeptCode;
- To retrieve the average SPI (Semester Performance Index) for each student:
SELECT RollNo, AVG(SPI) as AverageSPI
FROM Result
GROUP BY RollNo;
- To retrieve the students who have the highest SPI semester-wise:
SELECT Semester, RollNo, SPI
FROM Result
WHERE (Semester, SPI) IN (
SELECT Semester, MAX(SPI)
FROM Result
GROUP BY Semester
);
- To retrieve the students who belong to Ahmedabad city with their department names:
SELECT Student.Name, Department.DeptName
FROM Student
INNER JOIN Department ON Student.DeptCode = Department.DeptCode
WHERE Student.City = 'Ahmedabad';
Please replace the data types and sizes in the CREATE TABLE statements as per your requirements.
Similar Questions
Consider following schema and write SQL for given statements.Student (RollNo, Name, DeptCode, City)Department (DeptCode, DeptName)Result (RollNo, Semester, SPI)1. List out the RollNo, Name along with SPI of Student.2. Display student name who got highest SPI in semester 1.3. Display the list of students whose DeptCode is 5, 6,7,10
Problem StatementIn a university's academic department, a database table named "students" that stores information about students, including their student ID, name, enrolled course, and scores obtained in different subjects.The university administration wants to analyze the performance of students and identify those who scored above a certain threshold. To facilitate this analysis, you are tasked with creating a stored procedure named "GetHighScorers".The following table is already created, and the records are inserted into the table.TABLE: students student_id INT PRIMARY KEY, student_name VARCHAR(50), course VARCHAR(50), score INT
Consider following schema and write SQL statements for given queries using subqueries.Faculty (f_id, f_name, address, city, state, age, salary, d_id)Department (dep_id, d_name, course, location, sub_code, subject)Student (enrl_no, s_name, s_address, s_city, s_state, s_age, course, f_id, hobby)Create tables with Primary Key, foreign key constraints in given schemas.1) Display name of faculties and students who are not living in the same city.2) Find out how much amount is spent on faculties as their salary who are working in 'IT Engineering' Department.3) Display department name, course, subject code and subject of person who have enrolled as a student.4) Display id and name of faculty who are working in 'IT Engineering' department and have a salary more than Rs. 60000
Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade), for each student, get all the courses that he/she is enrolled in along with the grade he/she scored for each course. Order the result by the student name in ascending order and if there is a tie, break it with the course name in ascending order and if there is a tie break it with the grade in ascending order. Your query should output a table with the following columns (name, course, grade).
2.Question 2You are the database administrator for a school. Student information is kept in a students table where students are tracked by student_id. Students have a grade point average listed for each semester in a table named grades where each student is referenced by student_id.The school is giving an award to all students who achieved a 4.0 grade point average during any semester. You need to generate a list of all the students who should get the award. You begin typing your query:What should you type in the blank space? (Please type your answer all in lower case. Example: select)1 point
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.