Podcast
Questions and Answers
What SQL function is used to determine the total number of students enrolled?
What SQL function is used to determine the total number of students enrolled?
Which SQL operation would be used to find all major codes that students are registered in, ensuring that each code appears only once?
Which SQL operation would be used to find all major codes that students are registered in, ensuring that each code appears only once?
How would you retrieve the total balance of students grouped by their major code?
How would you retrieve the total balance of students grouped by their major code?
To count how many students live in Beirut, which SQL query is appropriate?
To count how many students live in Beirut, which SQL query is appropriate?
Signup and view all the answers
Which command would be used to delete all students that live in Beirut from the Student table?
Which command would be used to delete all students that live in Beirut from the Student table?
Signup and view all the answers
What data is being retrieved for students whose balance is outside the range of 200 to 1000?
What data is being retrieved for students whose balance is outside the range of 200 to 1000?
Signup and view all the answers
Which condition is used to filter out non CCE students from the data?
Which condition is used to filter out non CCE students from the data?
Signup and view all the answers
Which majors are included in the query that retrieves student ids and names for specified major codes?
Which majors are included in the query that retrieves student ids and names for specified major codes?
Signup and view all the answers
What is the purpose of the operation ∏(Major) - ∏(Student)?
What is the purpose of the operation ∏(Major) - ∏(Student)?
Signup and view all the answers
Which SQL operation is used to get student information living in a specific city?
Which SQL operation is used to get student information living in a specific city?
Signup and view all the answers
What does the symbol ∞ represent in the context of combining tables?
What does the symbol ∞ represent in the context of combining tables?
Signup and view all the answers
Which of the following queries retrieves the id, name, and city name of students?
Which of the following queries retrieves the id, name, and city name of students?
Signup and view all the answers
Which filter must be applied to identify Computer Communication students living in Beirut?
Which filter must be applied to identify Computer Communication students living in Beirut?
Signup and view all the answers
What is the purpose of the projection operator in relational algebra when applied to the City relation?
What is the purpose of the projection operator in relational algebra when applied to the City relation?
Signup and view all the answers
Which relational algebra expression gets the list of cities with a population less than 200?
Which relational algebra expression gets the list of cities with a population less than 200?
Signup and view all the answers
What does the selection operator do in the expression σ(balance ≥ 500 ∧ balance ≤ 5000)(Student)?
What does the selection operator do in the expression σ(balance ≥ 500 ∧ balance ≤ 5000)(Student)?
Signup and view all the answers
How is the union of two relations represented in relational algebra?
How is the union of two relations represented in relational algebra?
Signup and view all the answers
Which statement describes the function of the expression R1 U R2 in the context of the student data?
Which statement describes the function of the expression R1 U R2 in the context of the student data?
Signup and view all the answers
In the context of relational algebra, what does the notation mcode = 'CC' signify in the student relation?
In the context of relational algebra, what does the notation mcode = 'CC' signify in the student relation?
Signup and view all the answers
What is the result of the expression σ(population > 100)(City)?
What is the result of the expression σ(population > 100)(City)?
Signup and view all the answers
What attributes are retrieved by the expression ∏(sid, fn, ln, mcode, balance)(σ(mcode = 'CC' OR balance > 500)(Student))?
What attributes are retrieved by the expression ∏(sid, fn, ln, mcode, balance)(σ(mcode = 'CC' OR balance > 500)(Student))?
Signup and view all the answers
Study Notes
Relational Algebra Tutorial
-
Schema:
- City: (cityId, cityName, population)
- Major: (mcode, mname, mcredits)
- Student: (sid, fn, ln, balance, email, cityId*, mcode*)
-
- denotes foreign key
Queries
-
Get all cities:
- Π(City)
-
Get cities with population > 100:
- σ(population > 100) (City)
-
Get city IDs and names with population < 200:
- Π (σ(population < 200)(City)) (cityId, cityName)
-
Get student IDs, names, major codes, and balances with balance between 500 and 5000 (inclusive): -Π(σ(500 ≤ balance ≤ 5000)(Student)) (sid, fn, ln, mcode, balance)
-
Get student IDs, names, major codes, and balances for CC students or students with balance > 500:
- Π(σ(mcode = "CC" ∨ balance > 500)(Student)) (sid, fn, ln, mcode, balance)
-
Get student IDs, names, and balances where balance is not between 200 and 1000:
- Π(σ(balance < 200 ∨ balance > 1000)(Student)) (sid, fn, ln, balance)
-
Get student IDs, names, and city names of all students:
- Π(sid, fn, ln, cityName) (Student ∞ City)
-
Get student IDs, names, major codes, major names, and city names of non-CCE students:
- Π (σ(mcode ≠ "CCE")(City ∞ Student ∞ Major)) (sid, fn, ln, S.mcode, mname, cityName)
-
Get student IDs, names, major codes, and names of all CCE, CC, and CS students:
- Π (σ(mcode = "CCE" ∨ mcode = "CC" ∨ mcode = "CS")(Student)) (sid, fn, ln, mcode)
-
Get major codes of majors with no students:
- Π(mcode) (Major) - Π(mcode) (Student)
-
Get student IDs and names of students living in Beirut:
- Π (σ(cityName = "Beirut" ) (Student ∞ City)) (sid, fn, ln)
-
Get student IDs, names, and balances of Computer Communication students in Beirut: -Π (σ(cityName = "Beirut" ∧ mname = "Computer Communication")(City ∞ Student ∞ Major)) (sid, fn, ln, balance)
-
Get highest student balance:
- S MAX(balance) (Student)
-
Get lowest balance of all CC students:
- S MIN(balance) (σ(mcode = "CC")(Student))
-
Get total number of students:
- S COUNT(*) (Student)
-
Get total number of students in Beirut - S COUNT(*) (σ(cityName="Beirut")(Student∞City))
-
Get major code total balance:
- S SUM(balance) (Student)
-
Get total balance per major name:
- S SUM(balance) (Student ∞ Major)
-
Get IDs and names of students with emails:
- Π(sid, fn, ln) (σ(email ≠ NULL) (Student))
-
Get IDs and names of students without emails:
- Π(sid, fn, ln) (σ(email = NULL) (Student))
-
Delete students living in Beirut:
- student <- student - σ(cityName = "Beirut")(student ∞ city)
-
Add a new major:
- major <- major U {("MEE", "mechatronics", 110)}
-
Get distinct major codes:
- S COUNT(DISTINCT mcode) (Student)
-
Get total student count who own email:
- S COUNT(email) (Student)
-
Get all major codes with students:
- Π(DISTINCT mcode) (Student)
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
Explore the fundamentals of relational algebra through various queries and examples. This tutorial covers operations like selection and projection using schemas for cities, majors, and students. Test your understanding of how to manipulate and retrieve data effectively.
