SQL Concepts Matching Game
45 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Match the SQL components with their roles:

DML = Query and manipulate data DDL = Define database schema Transaction Control = Manage transaction boundaries Authorization = Specify access rights to data

Match the SQL data types with their descriptions:

char(n) = Fixed-length character string of length n varchar(n) = Variable-length character string with maximum length n int = Integer number numeric(p,d) = Fixed point number with precision p and d digits after decimal

Match the following SQL history events with their approximate dates:

IBM System R = 1970s SQL-86 = Mid 1980s SQL-92 = Early 1990s SQL:1999 = Late 1990s

Match the aspects of relation specification with their corresponding DDL function:

<p>Schema = Defines the structure of the relation Integrity constraints = Enforces data consistency and validity Indices = Improves query performance Authorization = Controls access permissions to the relation</p> Signup and view all the answers

Match each capability with the component of SQL that manages it:

<p>Creating tables = DDL Querying specific data = DML Setting user permissions = Authorization Committing updates = Transaction Control</p> Signup and view all the answers

Match the potential impact with the SQL feature that manages it:

<p>Data inconsistencies due to power failure = Transaction Control Unauthorized data access = Authorization Slow query execution = Indices Invalid data entry = Integrity constraints</p> Signup and view all the answers

Match the feature with an example of when it might be used:

<p><code>varchar(255)</code> = Storing names of varying lengths <code>numeric(10, 2)</code> = Storing monetary values <code>int</code> = Storing quantities of products in inventory <code>char(2)</code> = Storing state abbreviations</p> Signup and view all the answers

Match the characteristic with the historic SQL standard that introduced it:

<p>Basic query structure = SQL-86 More advanced features, like triggers = SQL-92 Object-relational features = SQL:1999 XML-related features = SQL:2003</p> Signup and view all the answers

Match the aggregate SQL functions with their descriptions:

<p>COUNT(*) = Returns the number of rows that match the specified criteria. AVG(column_name) = Calculates the average value of a specified column. SUM(column_name) = Calculates the total sum of a numeric column. MAX(column_name) = Determines the highest value in a specific column.</p> Signup and view all the answers

Match the SQL clauses with their purposes in a query:

<p>GROUP BY = Groups rows that have the same values into summary rows. HAVING = Filters group results based on a specified condition. WHERE = Filters individual rows based on a condition. ORDER BY = Sorts the result set of a query.</p> Signup and view all the answers

Match the following subquery placements with their roles in a SQL statement:

<p>FROM clause = Specifies a derived table to be used in the main query. WHERE clause = Implements a condition based on the result of the subquery. SELECT clause = Can provide a single, calculated value. HAVING clause = Used to filter results for aggregate functions.</p> Signup and view all the answers

Match the following SQL errors or constraints with their descriptions:

<p>Incorrect column in GROUP BY = Occurs when non-aggregated columns in the SELECT clause aren't in the GROUP BY clause. HAVING without GROUP BY = Occurs when using the <code>HAVING</code> clause without first grouping the data. Subquery returns more than one row when it shouldn't = Occurs if a subquery in the <code>WHERE</code> clause returns multiple rows and is used with a single-value comparison operator. Invalid use of aggregate function = Occurs when using aggregate functions in a <code>WHERE</code> clause.</p> Signup and view all the answers

Match the scenarios with the most appropriate SQL aggregate function or clause to use:

<p>Find the earliest date in a dataset = MIN(date_column) Determine if any departments have more than 5 instructors = HAVING COUNT(instructor_id) &gt; 5 after GROUP BY dept_name Calculate the total budget for each department = SUM(budget) with GROUP BY dept_name Identify the most frequent value in a column = Requires combining GROUP BY and COUNT(*), then ordering and limiting to find the mode</p> Signup and view all the answers

Match the SQL clause with its corresponding function:

<p>SELECT = Specifies the attributes to be included in the query result. FROM = Specifies the table(s) to retrieve data from. WHERE = Filters rows based on a specified condition. DISTINCT = Eliminates duplicate rows from the query result.</p> Signup and view all the answers

Match the following operations with their corresponding SQL command aspect:

<p>Projection = Corresponds to the <code>SELECT</code> clause, choosing specific attributes. Selection = Corresponds to the <code>WHERE</code> clause, filtering rows based on conditions. Rename attribute = Corresponds to the <code>AS</code> clause, giving an attribute a new name. All attributes = Specified by an asterisk <code>*</code> in the <code>SELECT</code> clause.</p> Signup and view all the answers

Match the following modifications with their corresponding SQL operations:

<p>Adding a new attribute to a relation = All existing tuples in the relation are assigned null as the value for the new attribute. Dropping an Attribute = Not supported by many databases. Case Sensitivity = SQL names are case insensitive Duplicates = SQL allows duplicates in relations as well as in query results.</p> Signup and view all the answers

Match the following SQL operations with the clause:

<p>Arithmetic Expressions = The <code>SELECT</code> clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or attributes of tuples. Literal without <code>from</code> clause = An attribute can be a literal with no <code>from</code> clause. Case Insensitive = Used in the from clause.</p> Signup and view all the answers

Match each SQL concept with its description:

<p>Attribute = A column in a table that stores a specific type of data. Relation = A table in a database, consisting of rows (tuples) and columns (attributes). Predicate = A condition or expression that evaluates to true or false, used in <code>WHERE</code> clauses. Tuple = A row in a table, representing a single record.</p> Signup and view all the answers

Match the functions with the appropriate SELECT clause expressions

<p>Choose all attributes = <code>SELECT *</code> Eliminate duplicate rows = <code>SELECT DISTINCT</code> Assign alias for column = <code>SELECT attribute AS alias</code> Compute numeric expressions = <code>SELECT attribute1 * attribute2</code></p> Signup and view all the answers

Match the following keywords to their functions in SQL queries:

<p>ALL = Specifies that duplicates should not be removed. AS = Used to rename an attribute or a computed value in a query. FROM = Specifies the table or tables from which to retrieve the data. WHERE = Filters the result set based on a specified condition.</p> Signup and view all the answers

Match the functionalities to the clauses typically used in SQL SELECT statements:

<p>Selecting specific columns from a table = <code>SELECT</code> Filtering rows based on conditions = <code>WHERE</code> Specifying the table to retrieve data from = <code>FROM</code> Removing duplicates from the result set = <code>DISTINCT</code></p> Signup and view all the answers

Match the SQL string operation with its appropriate function:

<p><code>'Intro%'</code> = Matches strings starting with 'Intro' <code>'\%Comp%'</code> = Matches strings containing 'Comp' <code>'\_ \_ \_'</code> = Matches strings of exactly three characters <code>'\_ \_ \_ %'</code> = Matches strings of at least three characters</p> Signup and view all the answers

Match the SQL ORDER BY clause with its corresponding sorting order:

<p><code>ORDER BY name</code> = Sorts in ascending order by name <code>ORDER BY name DESC</code> = Sorts in descending order by name <code>ORDER BY dept_name, name</code> = Sorts first by department, then by name <code>ORDER BY salary ASC</code> = Sorts in ascending order by salary</p> Signup and view all the answers

Match the SQL set operation with its corresponding action:

<p><code>UNION</code> = Combines results, removing duplicates <code>INTERSECT</code> = Returns common results from both sets <code>EXCEPT</code> = Returns results from the first set that are not in the second set <code>UNION ALL</code> = Combines results, including all duplicates</p> Signup and view all the answers

Match the following components of a WHERE clause with their correct function:

<p><code>BETWEEN</code> = Specifies a range within which a value must fall <code>LIKE</code> = Enables pattern matching in string comparisons <code>=</code> (with tuples) = Compares tuples for equality <code>&gt;</code> = Used to check if the value of an expression is greater than that of another expression</p> Signup and view all the answers

Match the examples with their intended purpose:

<p><code>name LIKE '%dar%'</code> = Finds names containing 'dar' <code>LIKE '100\%' ESCAPE '\'</code> = Matches the literal string '100%' <code>ORDER BY name</code> = Orders results alphabetically by name <code>salary BETWEEN 90000 AND 100000</code> = Filters salaries between $90,000 and $100,000</p> Signup and view all the answers

Relate each SQL command to its use in set operations:

<p><code>UNION</code> = Used to combine the result-sets of two or more SELECT statements (removing duplicate rows). <code>INTERSECT</code> = Returns only the rows found in both SELECT statements. <code>EXCEPT</code> = Returns the rows from the first SELECT statement that are not found in the second SELECT statement. <code>ALL</code> = Specifies that all rows (including duplicates) should be included in the result of a set operation.</p> Signup and view all the answers

Match each example with its equivalent description:

<p><code>(SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017) UNION (SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)</code> = Finds courses that ran in either Fall 2017 or Spring 2018 <code>(SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017) INTERSECT (SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)</code> = Finds courses that ran in both Fall 2017 and Spring 2018 <code>(SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017) EXCEPT (SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)</code> = Finds courses that ran in Fall 2017 but not in Spring 2018 <code>ORDER BY dept_name, name</code> = Specifies the order the result-set sorted by <code>dept_name</code> and then <code>name</code></p> Signup and view all the answers

Associate the below functions with the correct description:

<p><code>select distinct name from instructor</code> = Selects unique names from the instructor table <code>order by name desc</code> = Orders the result-set by name in descending order <code>where salary between 90000 and 100000</code> = Filters rows where salary is between 90000 and 100000 <code>name like '%dar%'</code> = Filters names containing 'dar'</p> Signup and view all the answers

Match the SQL clauses with their corresponding descriptions:

<p><code>IN</code> = Selects rows where a value matches any value in a list or subquery. <code>NOT IN</code> = Selects rows where a value does not match any value in a list or subquery. <code>&gt; SOME</code> = Selects rows where a value is greater than at least one value in a subquery. <code>&gt; ALL</code> = Selects rows where a value is greater than all values in a subquery.</p> Signup and view all the answers

Match the SQL queries with their intended outcomes regarding set operations on course_id from the section table:

<p>Find courses offered in Fall 2017 and in Spring 2018 = <code>SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2017 AND course_id IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2018)</code> Find courses offered in Fall 2017 but not in Spring 2018 = <code>SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2017 AND course_id NOT IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2018)</code> Find instructors whose name is neither Mozart nor Einstein = <code>SELECT DISTINCT name FROM instructor WHERE name NOT IN ('Mozart', 'Einstein')</code> Find the total number of distinct students who have taken course sections taught by the instructor with ID 10101 = <code>SELECT COUNT (DISTINCT ID) FROM takes WHERE (course_id, sec_id, semester, year) IN (SELECT course_id, sec_id, semester, year FROM teaches WHERE teaches.ID= 10101)</code></p> Signup and view all the answers

Match the mathematical expressions with their SQL equivalents using the SOME clause:

<p>$\exists t \in r$ such that $(5 &lt; t)$ = <code>5 &lt; SOME (SELECT column FROM table)</code> $\exists t \in r$ such that $(5 = t)$ = <code>5 = SOME (SELECT column FROM table)</code> $\exists t \in r$ such that $(5 eq t)$ = <code>5 &lt;&gt; SOME (SELECT column FROM table)</code> $\exists t \in r$ such that $(5 &gt; t)$ = <code>5 &gt; SOME (SELECT column FROM table)</code></p> Signup and view all the answers

Match the following scenarios with the appropriate SQL SOME clause comparisons:

<p>Find salaries greater than at least one salary in the Biology department. = <code>salary &gt; SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code> Find salaries equal to at least one salary in the Biology department. = <code>salary = SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code> Find salaries not equal to at least one salary in the Biology department. = <code>salary &lt;&gt; SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code> Find salaries less than at least one salary in the Biology department. = <code>salary &lt; SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code></p> Signup and view all the answers

Match SQL queries with their corresponding descriptions related to instructor salaries and departments:

<p>Find instructors with a salary greater than at least one instructor in the Biology department = <code>SELECT name FROM instructor WHERE salary &gt; SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code> Find instructors with a salary greater than all instructors in the Biology department = <code>SELECT name FROM instructor WHERE salary &gt; ALL (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code> Find instructors with a salary equal to at least one instructor in the Biology department = <code>SELECT name FROM instructor WHERE salary = SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code> Find instructors with a salary not equal to all instructors in the Biology department = <code>SELECT name FROM instructor WHERE salary &lt;&gt; ALL (SELECT salary FROM instructor WHERE dept_name = 'Biology')</code></p> Signup and view all the answers

Match each set comparison operator with its equivalent SQL clause:

<p>$\subseteq$ = NOT EXISTS (SELECT ... EXCEPT SELECT ...) $=$ = NOT EXISTS (SELECT ... EXCEPT SELECT ...) AND NOT EXISTS (SELECT ... EXCEPT SELECT ...) $\supset$ = NOT EXISTS (SELECT ... EXCEPT SELECT ...) $ eq$ = EXISTS (SELECT ... EXCEPT SELECT ...)</p> Signup and view all the answers

Match the following SQL clauses with their appropriate use cases:

<p><code>IN</code> clause = Checking if a value exists within a set of values. <code>NOT IN</code> clause = Checking if a value does not exist within a set of values. <code>SOME</code> clause = Comparing a value with at least one entry in a set. <code>ALL</code> clause = Comparing a value against every entry in a set.</p> Signup and view all the answers

Match the following SQL code snippets with their intended outcomes related to set comparison:

<p>Find instructors whose salary is greater than at least one instructor in 'Biology' = <code>SELECT name FROM instructor WHERE salary &gt; SOME (SELECT salary FROM instructor WHERE dept_name = 'Biology');</code> Find instructors whose salary is greater than all instructors in 'Biology' = <code>SELECT name FROM instructor WHERE salary &gt; ALL (SELECT salary FROM instructor WHERE dept_name = 'Biology');</code> Find courses offered in both 'Fall 2017' and 'Spring 2018' = <code>SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2017 AND course_id IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2018);</code> Find courses offered in 'Fall 2017' but not in 'Spring 2018' = <code>SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2017 AND course_id NOT IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2018);</code></p> Signup and view all the answers

Match the SQL clause with its function related to subqueries:

<p><code>WHERE</code> = Filters results based on the subquery's output. <code>FROM</code> = Allows using the result of a subquery as a virtual table. <code>WITH</code> = Defines temporary relations for use within a query. <code>SELECT</code> = Can include scalar subqueries to fetch a single value per row.</p> Signup and view all the answers

Match the type of subquery with its characteristic:

<p>Scalar subquery = Returns a single value. Subquery in <code>FROM</code> clause = Must be aliased with a name. Correlated subquery = Depends on the outer query's current row. <code>UNIQUE</code> predicate = Tests for the absence of duplicate rows.</p> Signup and view all the answers

Match the scenario with the appropriate SQL construct to use:

<p>Finding departments with a budget equal to the maximum budget = <code>WITH</code> clause to define a temporary relation for the maximum budget. Listing departments and their instructor counts = Scalar subquery in the <code>SELECT</code> statement. Filtering courses offered only once in a specific year = <code>UNIQUE</code> predicate in the <code>WHERE</code> clause. Calculating average salaries for departments with averages over $42,000 = Subquery in the <code>FROM</code> clause with a <code>WHERE</code> clause on the outer query.</p> Signup and view all the answers

Match the query objective with the appropriate SQL technique involving subqueries:

<p>Find departments where the total instructor salary exceeds the average total salary across all departments = Use two <code>WITH</code> clauses: one to calculate the total salary per department, and another to calculate the average of these totals. List all departments with the number of instructors in each department = Include a scalar subquery in the <code>SELECT</code> clause that counts instructors for each department. Find courses that were offered at most once in 2017 = Use a subquery with the <code>UNIQUE</code> predicate in the <code>WHERE</code> clause to check for unique course offerings. Calculate the average instructor salary for departments whose average salary is greater than $42,000 = Use a subquery in the <code>FROM</code> clause to first calculate each department's average salary, then filter in the outer query.</p> Signup and view all the answers

Match each SQL statement with its equivalent description of functionality:

<p><code>select dept_name, avg_salary from ( select dept_name, avg (salary) as avg_salary from instructor group by dept_name) as dept_avg (dept_name, avg_salary) where avg_salary &gt; 42000;</code> = Selects department names and their average salaries, filtering for those with an average salary greater than 42000. <code>with max_budget (value) as (select max(budget) from department) select department.name from department, max_budget where department.budget = max_budget.value;</code> = Finds all departments whose budget matches the maximum budget value across all departments. <code>select dept_name, ( select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructors from department;</code> = Lists each department along with the number of instructors in that department. <code>select T.course_id from course as T where unique ( select R.course_id from section as R where T.course_id= R.course_id and R.year = 2017);</code> = Finds courses that were offered at most once in the year 2017.</p> Signup and view all the answers

Match each SQL operation with the database modification it performs:

<p><code>DELETE FROM instructor WHERE dept_name = 'History';</code> = Removes all instructors from the 'History' department. <code>INSERT INTO course (course_id, title, dept_name, credits) VALUES ('CS-001', 'Intro to CS', 'CS', 3);</code> = Adds a new course with the specified details to the 'course' table. <code>UPDATE instructor SET salary = salary * 1.10 WHERE dept_name = 'CS';</code> = Increases the salary of all instructors in the 'CS' department by 10%. <code>CREATE TABLE student (ID VARCHAR(5), name VARCHAR(20), dept_name VARCHAR(20), tot_cred INT);</code> = Creates a new table named 'student' with columns for ID, name, department name, and total credits.</p> Signup and view all the answers

Match each concept related to SQL subqueries with its equivalent description:

<p>Subquery Aliasing = Assigning a name to a subquery result, necessary when used in the <code>FROM</code> clause. Correlated Subquery = A subquery that references a column from a table in the outer query. <code>WITH</code> Clause = Defines a temporary relation visible only within the scope of a single query. Scalar Subquery = A subquery that returns exactly one column and one row (a single value).</p> Signup and view all the answers

Match each scenario with the most appropriate use-case for SQL subqueries:

<p>Retrieving individual instructor details along with department totals = Scalar subquery to include aggregate data alongside individual row data. Find courses that were offered at most once in 2017 and also had a minimum enrollment of 20 students = Nested subqueries. One to filter unique courses and another to check the enrollment criteria. Calculating the total salary expenditure per department for departments with more than 5 instructors = A subquery in the <code>FROM</code> clause to filter relevant departments before calculating expenditure. Identifying departments whose total budget is greater than some global threshold = Use a subquery with 'WITH' to compute the thresold</p> Signup and view all the answers

Flashcards

SQL

A database language used for querying and managing data in relational database management systems (RDBMS).

SQL DDL

Part of SQL used to define the structure of the database, including creating, altering, and deleting tables.

SQL DML

Part of SQL used for retrieving, inserting, updating, and deleting data within the database tables.

Integrity Constraints

Statements used to ensure data accuracy and consistency within a database.

Signup and view all the flashcards

Relation Schema

Specifies the structure of a relation, including attribute names and their data types.

Signup and view all the flashcards

char(n)

Fixed-length character string with a specified length.

Signup and view all the flashcards

varchar(n)

Variable-length character string with a user-specified maximum length.

Signup and view all the flashcards

numeric(p, d)

Fixed-point number with user-specified precision (total digits) and scale (digits to the right of the decimal point).

Signup and view all the flashcards

COUNT(DISTINCT)

Counts distinct values in a column.

Signup and view all the flashcards

COUNT(*)

Counts all rows in a table.

Signup and view all the flashcards

GROUP BY

Groups rows with the same values in specified columns into summary rows.

Signup and view all the flashcards

HAVING clause

Filters groups based on a condition.

Signup and view all the flashcards

Nested Subquery

A SELECT statement nested inside another query.

Signup and view all the flashcards

Adding a new attribute

Assigns 'null' to all existing tuples for a new attribute added to a relation.

Signup and view all the flashcards

Dropping an attribute

Removes an attribute from a relation. Often not supported directly by many databases.

Signup and view all the flashcards

SELECT Clause

Defines the attributes to be selected in the query result.

Signup and view all the flashcards

FROM Clause

Specifies the tables from which to retrieve data.

Signup and view all the flashcards

WHERE Clause

Specifies a condition that tuples must satisfy to be included in the query result.

Signup and view all the flashcards

SELECT DISTINCT

Eliminates duplicate rows from the query result.

Signup and view all the flashcards

SELECT *

Specifies that all attributes from a relation should be included in the query result.

Signup and view all the flashcards

AS Clause

Specifies an alternative name for an attribute or expression in the query result.

Signup and view all the flashcards

LIKE '%substring%'

Matches strings containing a specified substring.

Signup and view all the flashcards

Escape Character in LIKE

Used in LIKE to match special characters; backslash is the escape character.

Signup and view all the flashcards

ORDER BY

Sorts the result-set of a query.

Signup and view all the flashcards

BETWEEN

Specifies a range of values (inclusive) in a WHERE clause.

Signup and view all the flashcards

UNION

Combines the results of two SELECT statements, removing duplicates.

Signup and view all the flashcards

INTERSECT

Returns only the common rows found in two SELECT statements.

Signup and view all the flashcards

EXCEPT

Returns rows from the first SELECT statement that are not in the second.

Signup and view all the flashcards

UNION ALL, INTERSECT ALL, EXCEPT ALL

Set operations that retain all duplicates.

Signup and view all the flashcards

Query for courses offered at most once in 2017

Finds courses offered at most once in 2017. It uses a subquery to check the uniqueness of course IDs within the section table for the year 2017.

Signup and view all the flashcards

Subqueries in FROM clause?

SQL allows the use of subquery expressions within the FROM clause.

Signup and view all the flashcards

WITH Clause

A temporary relation defined and available only to the query in which the WITH clause occurs.

Signup and view all the flashcards

Scalar Subquery

A subquery that returns a single value.

Signup and view all the flashcards

Deletion of Tuples

Used to remove tuples (rows) from a table.

Signup and view all the flashcards

Finding Departments with High Average Salaries

Finds departments where the average instructor salary is greater than $42,000 using a subquery in the FROM clause to first compute average salaries per department.

Signup and view all the flashcards

Benefits of WITH clause

The WITH clause lets you break the query into logical steps, improving code readability and maintainability

Signup and view all the flashcards

Listing Departments with Instructor Count

Lists all departments along with the number of instructors in each department, using a scalar subquery to count instructors per department.

Signup and view all the flashcards

Set Membership with 'in'

Finds courses offered in Fall 2017 that were also offered in Spring 2018. It uses a subquery with the 'in' operator to check for courses present in both semesters.

Signup and view all the flashcards

Set Exclusion with 'not in'

Finds courses offered in Fall 2017 that were not offered in Spring 2018. Uses 'not in' to exclude courses present in Spring 2018.

Signup and view all the flashcards

'not in' with literal values

This SQL query will select the distinct names of instructors, excluding 'Mozart' and 'Einstein'.

Signup and view all the flashcards

Counting Distinct IDs

Counts the unique number of students who have taken course sections taught by instructor with ID 10101. It uses a subquery to find the relevant course sections.

Signup and view all the flashcards

'> some' Clause

Finds instructors with a salary greater than at least one instructor in the Biology department. Uses the 'some' clause (salary > some(...)).

Signup and view all the flashcards

Definition of 'some' Clause

The 'some' clause checks if a condition is true for at least one element in a set. The expression F some r is true if there exists at least one t in r such that F t is true.

Signup and view all the flashcards

Finding Maximum Scalar Value

Finds instructors whose salary is greater than the salary of every instructor in the Biology department.

Signup and view all the flashcards

Definition of 'all' Clause

Requires a condition to be true for all elements in a set to return 'true'. It is usually used to perform comparison.

Signup and view all the flashcards

Study Notes

Introduction to SQL

  • Chapter 3 introduces database system concepts
  • Focus on key relational database language: SQL

History of SQL

  • IBM originally developed Sequel as part of System R project
  • Sequel became Structured Query Language (SQL)
  • ANSI and ISO created standard SQL versions, including SQL-86, SQL-89, SQL-92, SQL:1999, and SQL:2003
  • Commercial database systems typically offer SQL-92 features along with extensions

Structure of SQL

  • SQL includes DML for querying and modifying data
  • Integrity constraints are specified using DDL commands
  • View definitions use DDL commands
  • Transaction control manages transaction beginnings and endings
  • Embedded SQL and dynamic SQL statements utilize general-purpose programming languages
  • Authorization manages access rights to relations and views.

DDL (Data Definition Language)

  • DDL is used to specify information about relations:
  • Schemas
  • Value types for each attribute
  • Integrity constraints
  • Indices
  • Security and authorization
  • Physical storage structure on disk

Domain Types in SQL

  • char(n): Fixed-length character string with length n
  • varchar(n): Variable-length character string with maximum length n
  • int: Integer, machine-dependent
  • smallint: Small integer, machine-dependent
  • numeric(p,d): Fixed-point number with precision of p digits and d digits to the right of the decimal point
  • real, double precision: Floating-point numbers with machine-dependent precision
  • float(n): Floating-point number with user-specified precision of at least n digits

Create Table Construct

  • create table r (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1), ..., (integrity-constraintk))
  • r is the relation name
  • Ai is an attribute name in the schema of relation r
  • Di is the data type of values in the domain of attribute Ai
  • Example: create table instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

Integrity Constraints in Create Table

  • Primary key primary key (A1, ..., An)
  • Foreign key foreign key (Am, ..., An) references r
  • Not null not null
  • SQL prevents updates that violate integrity constraints
  • Example: create table instructor (ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department)

Updating Tables

  • Insertion: insert into instructor values ('10211', 'Smith', 'Biology', 66000)
  • Deletion: delete from student
  • Drop Table: drop table r
  • Alter Table: alter table r add A D (adds attribute A with domain D to relation r)

Basic SQL Query Structure

  • select A1, A2, ..., An from relation name(s), r1, r2, ..., rm where P
  • A represents an attribute
  • R represents a relation
  • P is a predicate
  • Result of an SQL query is a relation

The Select Clause

  • select clause lists attributes in query result
  • Equivalent to projection operation in relational algebra
  • Example: select name from instructor
  • SQL names are case-insensitive
  • distinct is used after select to remove duplicates
  • all specifies duplicates should not be removed
  • Asterisk (*) in select clause denotes "all attributes"

Literals in the Select Clause

  • An attribute can be a literal when there's no from clause
  • A table with one column will show the value of the select
  • A name can be given to the column using the as function
  • Example: select '437' as FOO
  • can be used with a from clause
  • It will generate table with one column and N rows (based on the number of rows in the table) each row as the value
  • Example
    • select 'A' from instructor
    • Result: is a table with one column and N rows (number of tuples in the instructors table), each row with value “A”

Arithmetic Expressions in Select Clause

  • The select clause may contain arithmetic expressions like +, -, *, /
  • Can rename using "as" clause
  • Example: select ID, name, salary/12 as monthly_salary

The Where Clause

  • where clause specifies query conditions
  • Corresponds to the selection predicate of the relational algebra
  • Example: select name from instructor where dept_name = 'Comp. Sci.'
  • Logical connectives are and, or, not.
  • Comparison operators are <, <=, >, >=, =, <>

The From Clause

  • Lists from which to select relations of the query
  • Corresponds to the Cartesian product operation of the relational algebra
  • Example: select * from instructor, teaches (generates every possible instructor-teaches pair)
  • Attributes are renamed for common attributes, like instructor.ID

Rename Operation

  • The as clause is used in SQL to rename relations and attributes
  • Format = old-name as new-name
  • The keyword as is optional
  • Example finding instructors with salary > some instructor in 'Comp. Sci': select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Comp. Sci.'

String Operations

  • like operator matches strings, using patterns
  • % character matches any substring
  • _ character matches any single character
  • Example: select name from instructor where name like '%dar%'
  • String matching is case sensitive.
  • Concatenation uses "||"
  • You can convert case and find string length

Ordering the Display of Tuples

  • Use order by clause to list alphabetically
  • Specify desc for descending or asc ascending order.
  • Example sorting: select distinct name from instructor order by name

Where Clause Predicates

  • between comparison operator is used to find values within a range
  • Example: select name from instructor where salary between 90000 and 100000

Set Operations

  • union returns combined results filtering for unique entries
  • intersect returns the values that the queries have in common
  • except returns value of the first query that the second query does not have
  • Example of extracting courses, combining queries for fall and spring semesters, (select course_id from section where sem = 'Fall' and year = 2017) union (select course_id from section where sem = 'Spring' and year = 2018)
  • union, intersect, except eliminate duplicates by default
  • use union all, intersect all, except all to keep all duplicates

Null Values

  • Tuples can have a null value in attributes
  • null means a value is unknown or doesn't exist
  • Any calculation with null results is null
  • is null checks for null values: select name from instructor where salary is null
  • is not null returns success if value is not null

Null Value Comparison

  • SQL considers any comparison with a null value as unknown, except for is null and is not null
  • Expressions involving null result in unknown value that must also be handled
  • Result of where clause is treated as false if evaluates to unknown

Aggregate Functions

  • Used to operate on a multiset of values in a column of a relation, returning one single value
  • avg: Average value
  • min: Minimum value
  • max: Maximum value
  • sum: Sum of values
  • count: Number of values
  • Example showing avg of instructors, select avg (salary) from instructor where dept_name= 'Comp. Sci.'

Aggregate Functions with Group By

  • Attributes outside aggregate functions must appear in group by list
  • Predicates in having clause are applied after forming groups versus predicates in where, applied before forming groups
  • Example: select dept_name, avg (salary) as avg_salary from instructor group by dept_name having avg (salary) > 42000

Nested Subqueries

  • SQL can nest subqueries
  • A subquery is a select-from-where expression nested within another query

Nested Subqueries Location

  • From clause: Any valid subquery can replace r₁, r₂, ..., rₘ
  • Where clause: Expressions can replace P: B SomeOperation (subquery)
  • Select clause: Aᵢ can be replaced a query that calculates a value

Set Membership Operations

  • Useful queries involve membership operations involving the keyword in
  • Example to find unique IDs: select distinct course_id from section where semester = 'Fall' and year= 2017 and course_id in (select course_id from section where semester = 'Spring' and year= 2018)

Set Comparison Operations

  • SQL includes two set comparison operators that include all and some

Some Clause

  • Use syntax > some
  • Example: where salary > some (select salary from instructor where dept name = 'Biology')

All Clause

  • Use syntax > all where the salary is greate than anything
  • Example: where salary > all (select salary from instructor where dept name = 'Biology')

Test for Empty Relations

  • exists checks for non-empty subquery return value r ≠ Ø
  • not exists means empty subquery r = Ø

Use of exists Clause

  • Example: select course_id from section as S where semester = 'Fall' and year = 2017 and exists (select * from section as T where semester = 'Spring' and year= 2018 and S.course_id = T.course_id)

Test for Absence of Duplicate Tuples

  • unique tests for subquery duplicate tuples
  • Evaluates to true if subquery contains no duplicate tuples
  • Example checking absence of duplicate tuples: select T.course_id from course as T where unique (select R.course_id from section as R where T.course_id = R.course_id and R.year = 2017)

Subqueries in the From Clause

  • Can be used as expressions
  • Example:select dept_name, avg_salary from (select dept_name, avg (salary) as avg_salary from instructor group by dept_name) where avg_salary > 42000

With Clause

  • Provides a way to define a temporary relation, definition available only to the query where the with clause happens
  • Example: with max_budget (value) as (select max(budget) from department) select department.name from department, max_budget where department.budget = max_budget.value

Complex Queries using with Clause

  • Example: with dept_total (dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name), dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.value > dept_total_avg.value

Scalar Subquery

  • Used where a single value is expected
  • Example to display all departments: select dept_name, (select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructors from department

Modification of the Database

  • Includes deletion of tuples, insertion of new tuples and updating of existing values

Deletion

  • Deletes all instructors: delete from instructor
  • Example deleting instructor: delete from instructor where dept name in (select dept name from department where building = 'Watson')
  • Example delete all instructots where their salary is lower than the average delete from instructor where salary < (select avg (salary) from instructor)

Insertion

  • Example adding a new tuple: insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4)
  • Make each student in the Music department who has earned more than 144 credit hours an instructor in the Music department with a salary of $18,000: insert into instructor select ID, name, dept name, 18000 from student where dept_name = 'Music' and total_cred > 144

Updates

  • Example raise by 5%: update instructor set salary = salary * 1.05
  • Example 2 if less than 70000: update instructor set salary = salary* 1.05 where salary <= 100000
  • Can use case statements for conditionals, if they are the same type

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

SQL Introduction - Database System Concepts 7th Ed. PDF

Description

Test your knowledge of SQL with this matching game. Match SQL components with their roles, data types with descriptions, and more. Enhance your understanding of SQL clauses, functions, and error handling.

More Like This

Use Quizgecko on...
Browser
Open
Browser
Browser