The standard language for defining tables, searching, inserting, updating and deleting data, and controlling privileges in relational databases. It is standardized by ISO/IEC. Its statements include SELECT, INSERT, UPDATE, DELETE, CREATE TABLE and GRANT.
Japanese name: SQL
SQL appears in 15 practice questions across 3 topics, including Database Basics, Information Security Basics, IC design flow and EDA.
Field: Database Basics
SQL statements fall into three broad groups by role. The data definition language (DDL), such as CREATE, ALTER and DROP, defines structures like tables, views and indexes. The data manipulation language (DML), such as SELECT, INSERT, UPDATE and DELETE, handles the data in tables. The data control language (DCL), such as GRANT and REVOKE, controls privileges. COMMIT, which makes a transaction permanent, and ROLLBACK, which cancels it, are sometimes counted as DCL and sometimes treated separately as transaction control statements.
A SELECT statement is written by listing clauses, as in SELECT columns FROM table WHERE condition GROUP BY columns HAVING condition ORDER BY columns. Unlike the written order, it is easier to understand the processing as FROM (choose and join tables), WHERE (select rows), GROUP BY (form groups), HAVING (select groups), SELECT (take columns and compute aggregate functions) and ORDER BY (sort). This is why aggregate functions cannot be written in WHERE and conditions on groups go in HAVING.
SQL is a declarative language in which you write what result you want, and the DBMS optimizer decides the steps to obtain it. Care is needed with NULL, which represents an unknown value. A comparison with NULL is neither true nor false (unknown), so it is tested with IS NULL rather than = NULL. Aggregate functions such as SUM and AVG ignore NULL; COUNT(*) counts rows, while COUNT(column) counts rows where that column is not NULL.
Answer: B. CREATE TABLE department (dept_id INTEGER PRIMARY KEY, dept_name CHAR(10))
DDL (Data Definition Language) statements define the structure of a database, such as tables, views and indexes, and include CREATE, ALTER and DROP. DML (Data Manipulation Language) statements handle the data inside tables and include SELECT, INSERT, UPDATE and DELETE. DCL (Data Control Language) statements control things such as privileges and include GRANT, which gives a user privileges, and REVOKE, which takes them away. COMMIT, which makes a transaction permanent, and ROLLBACK, which cancels it, are sometimes counted as DCL and sometimes treated separately as transaction control statements. Among the choices, CREATE TABLE, which creates a table, is DDL.
Answer: A. All rows of the employee table are deleted, and an empty table remains
DELETE is a DML statement that removes rows from a table. With a condition in a WHERE clause, only the rows that meet it are deleted; if the WHERE clause is omitted, every row is deleted. Even after DELETE removes all rows, the table definition (column names, types and constraints) remains, so rows can be added again with INSERT. Deleting the table itself, definition included, is done with the DDL statement DROP TABLE employee. The same applies to UPDATE: forgetting the WHERE clause rewrites every row, so it is important to check the condition before running it. Inside a transaction, the change can be cancelled with ROLLBACK before COMMIT.
The WHERE clause is a condition on individual rows before grouping, and aggregate functions cannot be written in it. The HAVING clause is a condition on the groups formed by GROUP BY, and it can use aggregate functions, as in HAVING SUM(amount) >= 1000.
Write WHERE column IS NULL. WHERE column = NULL selects no rows, because a comparison with NULL is never true. Rows that are not NULL are selected with IS NOT NULL.
Think of SQL processing in this order: WHERE selects rows from the table in FROM, GROUP BY gathers them into groups, and HAVING selects groups.
From an employee table (emp_no, dept, salary), you want the…The SQL standard defines four isolation levels, in order of how few phenomena they prevent: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.
Among the isolation levels defined by the SQL standard, whi…HTML describes web pages and SQL queries databases; neither can describe circuits.
Which hardware description languages are mainly used in RTL…SQL injection exploits a web application that builds database commands (SQL statements) by simply concatenating user input as strings.
In a web application, entering a string such as ' OR '1'='1…With a prepared statement, an SQL statement whose values are replaced by symbols (placeholders), such as "SELECT … WHERE id = ?
As a countermeasure against SQL injection, why are prepared…As an Amazon Associate I earn from qualifying purchases.
Transaction Isolation level Primary key SQL injection Relational database RTL (register-transfer level) Verilog Hardware description language (HDL) SystemVerilog ACID properties