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
Question
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
Solution
- To list out the RollNo, Name along with SPI of Student, you can use the following SQL statement:
SELECT Student.RollNo, Student.Name, Result.SPI
FROM Student
JOIN Result ON Student.RollNo = Result.RollNo;
- To display the student name who got the highest SPI in semester 1, you can use the following SQL statement:
SELECT Student.Name
FROM Student
JOIN Result ON Student.RollNo = Result.RollNo
WHERE Result.Semester = 1
ORDER BY Result.SPI DESC
LIMIT 1;
- To display the list of students whose DeptCode is 5, 6,7,10, you can use the following SQL statement:
SELECT *
FROM Student
WHERE DeptCode IN (5, 6, 7, 10);
These SQL statements assume that the tables are joined based on the RollNo in the Student and Result tables, and that the highest SPI is considered to be the best.
Similar Questions
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
Give the database schema the following tables: Student (studentId, firstName, lastName, gender, birthday, phone, address, classId) Class (classId, className) Which code snippet will display records with the following 3 columns: classId, className and amount (amount of students)?SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classId;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s WHERE c.classId = s.classIdGROUP BY c.classId, c.className;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.className;
Give the database schema the following tables:Student (studentId, firstName, lastName, gender, birthday, phone, address, classId)Class (classId, className)Which code snippet will list the id and names of class with more than 15 students?SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.classNameHAVING COUNT(*) > 15;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student sWHERE c.classId = s.classId AND amount > 15GROUP BY c.classId, c.className;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.classNameHAVING amount > 15;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdWHERE COUNT(*) > 15;
Write queries for the following.Student_Result (Roll_No, Enrollment_No , Name, Div, Branch, Mentor , DBMS , DS ,JAVA_II , MATHS_II , FEE)(1) Display branchwise count of student in descending order.(2) Display the roll no, name and mentor of student who got less than 9 marks in anysubject.(3) Display the branchwise maximum total marks of all subject
Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade), find the name of the most popular course (the one with the most enrolled students and students should be passing, i.e. achieving a grade of at least 50) and if there is a tie, get the course name that's lexicographically smaller. Your query should output a table with the following columns (name)
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.