[ DB ] 04. SQL DML

38A·2023년 4월 24일
1

Database System

목록 보기
4/10
post-thumbnail

🖥️ Structured query language (SQL)

  • SQL: Structured Query Language

    • The principal language used to describe and manipulate relational
      databases
    • Very high-level
      • Say “what to do” rather than “how to do it”
      • DBMSs figure out the “best” way to execute queries
        ➡️ Called “query optimization”

  • Two aspects to SQL

    • Data definition: for declaring database schemas(table structure) (DDL)
    • Data manipulation: for querying (asking questions about) databases and for
      modifying the database (DML)

  • Transaction control – includes commands for specifying the beginning and ending of transactions

  • Authorization – includes commands for specifying access rights to relations and views

  • Embedded SQL and dynamic SQL – define how SQL statements can be embedded within general-purpose programming language ➡️ ACL(Access Control List)

A Brief History

  • IBM SEQUEL (Structured English Query Language) was developed as a part of the System R project

    • Later on, SEQUEL was renamed SQL (structured query language)
    • System R ➡️ System/38 (1979), SQL/DS (1981), DB2 (1983) ➡️ still available

  • Relational Software, Inc released the first commercial implementation of SQL, Oracle V2 for VAX computers

    • Relational Software, Inc is now Oracle Corporation

  • ANSI and ISO standardized SQL:

    • SQL-86, SQL-89, SQL-92, SQL:1999, ..., SQL:2011, SQL:2016 (current)
    • SQL-92 is supported by the most of database systems

🖥️ SQL data manipulation language (DML)

SELECT

  • The SELECT clause
    • The SELECT clause lists the attributes desired(보기원하는) in the result of a query
      - Corresponds to the projection operation of the relational algebra
    • ⭐️ SQL names are case insensitive
      - Ex_ Name ≡ NAME ≡ name
      - SQL commands are written in upper case (just a convention)
      - MySQL has an option flag, lower_case_table_names = true ➡️ case-sensitive

  • SQL allows duplicates in relations as well as in query results (default = ALL)
    ➡️ DISTINCT for Duplicate remove
    Ex_ SELECT DISTINCT dept_name FROM instructor;

  • An asterisk in the select clause denotes “all attributes”
    SELECT * FROM instructor;

  • An attribute can be a literal with no FROM clause
    SELECT ‘437’;
  • An attribute can be a literal with FROM clause
    SELECT 'A' FROM instructor

WHERE

  • The WHERE clause specifies condition that the result must satisfy
    • Corresponds to the selection predicate of the relational algebra
  • SQL allows the use of the logical connectives AND, OR, and NOT
  • comparison operators <, <=, >, >=, =, and <> ➡️ No ==, != in SQL

  • BETWEEN comparison operator
  • Ex_ Find the names of all instructors with salary between $90,000 and $100,000
    (that is, >= $90,000 and <= $100,000)
    • SELECT name
      FROM instructor
      WHERE salary BETWEEN 90000 AND 100000

  • Tuple comparison: makes comparisons per tuple
    • SELECT name, course_id
      FROM instructor, teaches
      WHERE (instructor.ID, dept_name) = (teaches.ID, 'Biology');

FROM

  • The FROM clause lists the relations involved in the query
    • Corresponds to the Cartesian-product operation of the relational algebra

  • Implementing JOIN
    • Cartesian-product is not very useful directly; but useful combined with WHERE-clause condition
    • Cartesian-product + selection = join
    • Ex_ Find the names of all instructors in the Music department who have taught some course and the course_id
    • SELECT name, course_id
      FROM instructor , teaches
      WHERE instructor.ID = teaches.ID AND instructor. dept_name = 'Music'

AS

  • Ex_ Find the names of all instructors who have a higher salary than 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.'
  • Keyword AS is optional and may be omitted (생략가능)
    instructor AS T ≡ instructor T

NULL values

  • It is possible for tuples to have a NULL value for some of their attributes
    • NULL signifies an unknown value or that a value does not exist
  • The result of any arithmetic expression involving(포함한) NULL is NULL
    • 5 + NULL returns NULL

  • The predicate IS NULL can be used to check for NULL values
  • The predicate IS NOT NULL succeeds if the value on which it is applied is not null

Set operations

  • Set operations UNION, INTERSECT, and EXCEPT
    • Each of the above operations automatically eliminates duplicates ➡️ retain duplicate using ALL

UNION

Ex_ (SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)
UNION
(SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018)

INTERSECT

➡️ MySQL does NOT support INTERSECT

EXCEPT

➡️ MySQL does NOT support EXCEPT, using NOT IN
Ex_ SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017
AND course_id NOT IN
(SELECT course_id FROM teaches
WHERE semester = 'Spring' AND year = 2018);

String operations

  • SQL includes a string-matching operator for comparisons on character strings
  • LIKE Operation
    • percent (%) – The % character matches any substring
    • underscore (_) – The _ character matches any character
  • Escape character: Use backslash (\) as the escape character
    • E.g., Match the string “100%”
      LIKE '100 \%’ ESCAPE ‘\’ ➡️ default '\'
      LIKE '100#%' ESCAPE '#'
  • Patterns are case sensitive
    • 'Intro%' matches any string beginning with “Intro”
    • '%Comp%' matches any string containing “Comp” as a substring
    • '_ _ _' matches any string of exactly three characters
    • '_ _ _ %' matches any string of at least three characters
  • SQL supports a variety of string operations such as
    • Concatenation (using “||”), LOWER(), REGEXP ...

Ordering

  • ORDER BY Operation
    • dafault ASC, using DESC for descending order

Aggregate functions

  • Aggregate Functions
    • AVG: average value
    • MIN: minimum value
    • MAX: maximum value
    • SUM: sum of values
    • COUNT: number of values
    • Group By
      - /* erroneous query */
      SELECT dept_name, ID, AVG(salary)
      FROM instructor
      GROUP BY dept_name;
    • HAVING
      • HAVING vs. WHERE
      • HAVING clause are applied after the formation of groups
      • But WHERE clause are applied before forming groups

➡️ SELECT FROM r LIMIT 5; // 5개 추출 (0-4)
SELECT FROM r LIMIT 4, 10; // 5번째부터 10개 추출. First parameter is 0-indexed!)

INSERT

INSERT INTO tablename
VALUES (col1_value, col2_value, ...)

  • Must list values in the same order as in the table schema
  • If some data values are unknown, must type NULL
  • For character sequences, use quotation marks
    • Single quotation marks are preferred (but double quotation marks are allowed)
    • Value in quotations is case-sensitive
  • Insert data into selected columns
    • INSERT INTO tablename (col1_name, col3_name, col4_name, ...)
      VALUES (col1_value, col3_value, col4_value, ...)
      ➡️ col2 X
  • Ex_ Add a new tuple to student with tot_creds set to null
    INSERT INTO student
    VALUES ('3003', 'Green', 'Finance', null);

  • A foreign key specifies that an attribute from one relation has to map to a tuple in another relation
    • Value in one relation must appear in another relation

  • Inserting results of other SELECT query
    • 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(literal)
          FROM student
          WHERE dept_name = 'Music’ AND total_cred > 144
          );
  • The SELECT FROM WHERE statement is evaluated fully before any of its results are inserted into the relation
    • Otherwise queries like
      INSERT INTO table1 SELECT * FROM table1
      would cause problem ➡️ Order가 정해져 있음 (SELECT 이후 INSERT)

UPDATE

UPDATE tablename
SET col1_name = new_col1_value, col2_name = new_col2_value, ...;
WHERE predicate;

Ex_ Give a 5% salary raise to instructors whose salary is less than average
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < ( SELECT AVG(salary) FROM instructor );

  • The order is important
    Ex_ Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%
    • UPDATE instructor
      SET salary = salary * 1.03
      WHERE salary > 100000;
    • UPDATE instructor
      SET salary = salary * 1.05
      WHERE salary <= 100000;
      ➡️ 순서 반대로 실행 시 중복 수혜의 가능성이 있음
  • CASE Statement for Conditional Update
UPDATE instructor 
SET salary = CASE
		WHEN salary <= 100000 THEN salary * 1.05
		ELSE salary * 1.03 
	END
  • UPDATE with Scalar Subqueries
    • Recompute and update tot_creds value for all students 
UPDATE student S
SET tot_cred = (
  	SELECT SUM(credits)
	FROM takes, course
	WHERE takes.course_id = course.course_id AND
		S.ID= takes.ID AND 
  		takes.grade <> 'F' AND 
  		takes.grade IS NOT NULL
);

DELETE

  • To remove specific rows
    • DELETE FROM tablename
      WHERE predicate;
  • To remove all rows
    • DELETE FROM tablename;
    • TRUNCATE (TABLE) tablename;

Ex_ Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building

DELETE FROM instructor
WHERE dept_name IN (
  	SELECT dept_name
	FROM department
	WHERE building = 'Watson'
);
  • Issue: as we delete tuples from instructor, the average salary changes
    Ex_ DELETE FROM instructor
    WHERE salary < (SELECT AVG (salary) FROM instructor);
    • Solution used in SQL:
    1. First, compute AVG(salary) and find all tuples to delete
    2. Next, delete all tuples found above (without recomputing AVG or retesting the tuples)

Nested subqueries

  • SQL provides a mechanism for the nesting of subqueries. A subquery is a SELECT-FROM-WHERE expression that is nested within another query
  • FROM clause: ri_i can be replaced by any valid subquery
  • WHERE clause: P can be replaced with an expression of the form:
    B <operation> (subquery)
    B is an attribute and to be defined later
  • SELECT clause:
    Ai_i can be replaced be a subquery that generates a single value
    (scalar subquery)

Ex_ Find the average instructors’ salaries of those departments where the average salary is greater than $42,000

SELECT D.dept_name, D.avg_salary
FROM ( 
  	SELECT dept_name, AVG(salary) AS avg_salary
	FROM instructor
	GROUP BY dept_name
) AS D 
WHERE D.avg_salary > 42000;

= GROUP BY dept_name HAVING avg > 42000

WITH Clause

  • The WITH clause provides a way of defining a temporary relation

Ex_ Find all departments with the maximum budget

WITH max_budget (value) AS (
  	SELECT MAX(budget)
	FROM department
)
SELECT department.dept_name
FROM department, max_budget
WHERE department.budget = max_budget.value;
  • temporary relation(table)
    • table name : max_buget
    • column : value
    • content : SELECT MAX(budget) FROM department

➡️ 이 쿼리가 종료되면 WITH 해제

Scalar Subquery

  • Scalar subquery is used where a single value is expected
  • Runtime error occurs if a subquery returns more than one result tuple

Ex_ List all departments along with the number of instructors in each department

SELECT dept_name, 
  	( SELECT COUNT(*)
	FROM instructor
	WHERE department.dept_name = instructor.dept_name
    ) AS num_instructors
FROM department;

Set membership (SOME, ALL, EXISTS)

IN ➡️ Set - intersection

Ex_ Find courses offered in Fall 2017 and in Spring 2018

SELECT DISTINCT course_id
FROM teaches
WHERE semester = 'Fall' AND year= 2017 AND
	course_id IN (
  		SELECT course_id 
  		FROM teaches
		WHERE semester = 'Spring' AND year= 2018
 	);

NOT IN ➡️ Set - difference

Ex_ Name all instructors whose name is neither “Mozart” nor Einstein”

SELECT DISTINCT name
FROM instructor
WHERE name NOT IN ('Mozart', 'Einstein’);

Ex2_ Find the total number of unique students who have taken course sections taught by the instructor with ID 10101

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
);

SOME - Set Comparison

F <comp> SOME r

Ex_ Find names of instructors with salary greater than that of SOME (at least one) instructor in the Biology department

SELECT name
FROM instructor
WHERE salary > SOME(
  	SELECT salary
	FROM instructor
	WHERE dept_name = 'Biology'
);

ALL - Set Comparison

Ex_ Find the names of ALL instructors whose salary is greater than the salary of ALL instructors in the Biology department

SELECT name
FROM instructor
WHERE salary > ALL (
  	SELECT salary
  	FROM instructor
  	WHERE dept_name = 'Biology'
);

EXISTS

Ex_ Yet another way of specifying the query “Find all courses taught in both the Fall 2017 semester and in the Spring 2018 semester”

SELECT course_id
FROM teaches AS T1
WHERE semester = 'Fall' AND year = 2017 AND
	EXISTS (
 		SELECT *
  		FROM teaches AS T2
  		WHERE semester = 'Spring' AND year = 2018 AND
  		T1.course_id = T2.course_id
  	);

NOT EXISTS

⭐️ Ex_ Find all students who have taken all courses offered in the Music department

SELECT DISTINCT S.ID, S.name
FROM student AS S
WHERE NOT EXISTS ( 
	SELECT course_id
  	FROM course
  	WHERE dept_name = 'Music' AND
  		course_id NOT IN (
  			SELECT T.course_id
  			FROM takes AS T
  			WHERE S.ID = T.ID
  		)
);

➡️ Some systems support the EXCEPT clause (MySQL does not)

UNIQUE

  • The UNIQUE construct tests whether a subquery has any duplicate tuples in its result
    • Evaluates to “true” if a given subquery contains no duplicates
    • MySQL does not support the UNIQUE test (UNIQUE in MySQL is a constraint specifier)
  • Find all courses that were offered at most once in 2017
SELECT T.course_id
FROM course AS T
WHERE UNIQUE ( 
  	SELECT R.course_id
	FROM teaches AS R
	WHERE T.course_id= R.course_id AND R.year = 2017
);

HGU 전산전자공학부 홍참길 교수님의 23-1 Database System 수업을 듣고 작성한 포스트이며, 첨부한 모든 사진은 교수님 수업 PPT의 사진 원본에 필기를 한 수정본입니다.

profile
38A
HGU - 개인 공부 기록용 블로그
이전 포스트

[ DB ] 03. MySQL

다음 포스트

[ DB ] 05. SQL DDL

0개의 댓글