# Competent

• Creating, modifying, removing database objects

• Aggregations (ORDER BY, GROUP BY, HAVING, SUM, COUNT, AVG, etc)

• Combining the results of multiple queries (union, except, intersect, minus, subqueries)

• Sessions, transactions, locks

• Implementing stored procedures, user-defined functions, triggers

• Cursors

# Creating, modifying, removing database objects

# operations with Database

• create database

  CREATE DATABASE DatabaseName;
  

• remove database

  DROP DATABASE DatabaseName;
  

• select database

  USE DatabaseName;
  

# DESC

print table

DESC table_name;

# CREATE TABLE

Syntax

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

CREATE TABLE CUSTOMERS(
  ID INT NOT NULL,
  NAME VARCHAR (20) NOT NULL,
  AGE INT NOT NULL,
  ADDRESS CHAR (25),
  SALARY DECIMAL (18, 2),
  PRIMARY KEY (ID)
);

Result

# DROP TABLE

DROP TABLE table_name;

# INSERT INTO

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database

Syntax

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

Example

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS 
VALUES (2, 'Muffy', 24, 'Indore', 10000.00 );

# Populate one table using another table

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
SELECT column1, column2, ...columnN 
FROM second_table_name
[WHERE condition];

# ALTER TABLE

Used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table.

Syntax

• new column

  ALTER TABLE table_name ADD column_name datatype;
  

• drop table

  ALTER TABLE table_name DROP COLUMN column_name;
  

• change data type

  ALTER TABLE table_name MODIFY COLUMN column_name datatype;
  

• add NOT NULL constraint to column

  ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
  

• add UNIQUE constrant

  ALTER TABLE table_name 
  ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
  

• add CHECK constraint

  ALTER TABLE table_name 
  ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
  

• add PRIMARY KEY

  ALTER TABLE table_name 
  ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
  

• drop constraint

ALTER TABLE table_name 
DROP CONSTRAINT MyUniqueConstraint;

# Aggregations (ORDER BY, GROUP BY, HAVING, SUM, COUNT, AVG, etc)

# ORDER BY

Syntax

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.

Example

SELECT *
FROM CUSTOMERS
ORDER BY NAME DESC

# GROUP BY

The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

Syntax

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

SELECT NAME, SUM(SALARY)
FROM CUSTOMERS
GROUP BY NAME;

# HAVING

Syntax

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

Following is an example, which would display a record for a similar age count that would be more than or equal to 2.

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

# SUM

Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

# COUNT

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

# AVG (среднее значение)

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

# Combining the results of multiple queries (union, except, intersect, minus, subqueries)

# UNION

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

# EXCEPT

Syntax

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

To retain duplicates, we must explicitly write EXCEPTALL instead of EXCEPT

# INTERSECT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

# MINUS

MINUS operator will return only those rows which are present in the result set from Table1 and not present in the result set of Table2.

Syntax

SELECT column1 , column2 , ... columnN
FROM table_name
WHERE condition
MINUS
SELECT column1 , column2 , ... columnN
FROM table_name
WHERE condition;

Example

SELECT NAME, AGE , GRADE
FROM Table1
MINUS 
SELECT NAME, AGE, GRADE 
FROM Table2

# subqueries

A subquery is a SQL query nested inside a larger query.

• A subquery may occur in:
  • A SELECT clause
  • A FROM clause
  • A WHERE clause
• The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
• A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
• You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
• A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
• The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.

You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to perform the following tasks:

• Compare an expression to the result of the query.
• Determine if an expression is included in the results of the query.
• Check whether the query selects any rows.

Example

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid = 'V002');

# Sessions, transactions, locks

# Sessions

Whatever happens in terms of communication between an RDBMS server and a user accessing it happens in the context of a session. In a multiuser environment, one of the primary concerns is data integrity. When a client application establishes a connection to an RDBMS server, it is said that it opens a session. The session becomes this application's private communication channel. The user of the application may change some preferences within the session (for example, default language or default date format); these settings would affect only the session environment and remain valid only for the duration of the session. The details of the implementation and default behavior of the sessions might differ among the RDBMS, but these basic principles always remain the same.

The SQL standard specifies a number of parameters that could be set in a session None of these are implemented directly by the RDBMS, though some elements made it into proprietary syntax, ditching the letter, preserving the spirit.

A SQL session is an occurrence of a user interacting with a relational database through the use of SQL commands. When a user initially connects to the database, a session is established. Within the scope of a SQL session, valid SQL commands can be entered to query the database, manipulate data in the database, and define database structures, such as tables. A session may be invoked by either direct connection to the database or through a front-end application. In both cases, sessions are normally established by a user at a terminal or workstation that communicates through a network with the computer that hosts the database.

Welcome to the World of SQL By Ron Plew and Ryan Stephens Oct 4, 2002 📄 Contents

␡ SQL Definition and History SQL Sessions Types of SQL Commands An Introduction to the Database Used in This Book Summary Q&A Workshop ⎙ Print + Share This < Back Page 2 of 7 Next > This chapter is from the book  This chapter is from the book Sams Teach Yourself SQL in 24 Hours, 3rd EditionSams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy This chapter is from the book Sams Teach Yourself SQL in 24 Hours, 3rd EditionSams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy SQL Sessions A SQL session is an occurrence of a user interacting with a relational database through the use of SQL commands. When a user initially connects to the database, a session is established. Within the scope of a SQL session, valid SQL commands can be entered to query the database, manipulate data in the database, and define database structures, such as tables. A session may be invoked by either direct connection to the database or through a front-end application. In both cases, sessions are normally established by a user at a terminal or workstation that communicates through a network with the computer that hosts the database.

CONNECT When a user connects to a database, the SQL session is initialized. The CONNECT command is used to establish a database connection. With the CONNECT command, you can either invoke a connection or change connections to the database. For example, if you are connected as USER1, you can use the CONNECT command to connect to the database as USER2. When this happens, the SQL session for USER1 is implicitly disconnected.

CONNECT user@database

When you attempt to connect to a database, you are automatically prompted for a password that is associated with your current username. The username is used to authenticate yourself to the database, and the password is the key that allows entrance.

DISCONNECT and EXIT When a user disconnects from a database, the SQL session is terminated. The DISCONNECT command is used to disconnect a user from the database. When you disconnect from the database, the software you are using may still appear to be communicating with the database, but you have lost your connection. When you use EXIT to leave the database, your SQL session is terminated and the software that you are using to access the database is normally closed.

# Transactions

A transaction complements the concept of the session with additional granularity — it divides every operation that occurs within the session into logical units of work. In this way, database operations — those involving data and structure modifications — are performed step-by-step and can be rolled back at any time, or committed if every step is successful. The idea of the transaction is to provide a mechanism for ensuring that a multistep operation is performed as a single unit. If any of the steps involved in a transaction fails, the whole transaction is rolled back. If all steps have been completed successfully, the transaction can be either committed (to save all the changes into a database) or rolled back to undo all the changes. The SQL standard defined transactions from the very beginning and enhanced the concept during subsequent iterations. According to the standard, a transaction is started automatically by RDBMS and continues until COMMIT or ROLLBACK statements are issued; the details were left for the vendors to implement.

A transaction must pass the ACID test:

• Atomicity. Either all the changes are made or none.
• Consistency. All the data involved into an operation must be left in a consistent state upon completion or rollback of the transaction; database integrity cannot be compromised.
• Isolation. One transaction should not be aware of the modifications made to the data by any other transaction unless it was committed to the database. Different isolation levels can be set to modify this default behavior.
• Durability. The results of a transaction that has been successfully committed to the database remain there.

The transaction model, as it is defined in the ANSI/ISO standard, utilizes the implicit start of a transaction, with an explicit COMMIT, in the case of successful execution of all transactions logical units, or an explicit ROLLBACK, when the noncommitted changes need to be rolled back (e.g., when program terminates abnormally).

# Locks

Concurrency is one of the major concerns in a multiuser environment. When multiple sessions write or read data to and from shared resources, a database might loose its integrity. To prevent this from happening, every RDBMS worth its salt implements a concurrency control mechanisms. In the case of RDBMS servers, the concurrency is managed through various locking mechanisms. All three leading RDBMS vendors have implemented sophisticated mechanisms for concurrency management.

Each session receives a read-consistent image of the data. Thus, even if some other process has begun modifying data in the set but did not commit the changes, every subsequent session will be able to read the data just as it was before; once the changes are committed in the first session, every other session is able to see it. The locks are acquired only when the changes are being committed to the database. Oracle automatically selects the least-restrictive lock. User can choose to manually lock a resource (a table, for example). In this case, other users still might be able to access the data, depending on the type of lock deployed.

Most of the time, a user does not have to worry about locking, as RDBMS automatically select the most appropriate lock (or locks) for a particular operation; only if this programmed logic fails should you attempt to specify the locks manually, using the SQL statements.

There are two broad categories of concurrency — optimistic and pessimistic. The names are self-explanatory. Transactions with optimistic concurrency work on the assumption that resource conflicts — when more than one transaction works on the same set of data — are unlikely (though possible). Optimistic transactions check for potential conflicts when committing changes to a database and conflicts are resolved by resubmitting data. Pessimistic transactions expect conflicts from the very beginning and lock all resources they intend to use. Usually RDBMS employ both optimistic and pessimistic transactions, and users can instruct their transactions to use either.

# Implementing stored procedures, user-defined functions, triggers

Performance and network traffic

Stored routines can be used to improve application performance. Since they simply appear to be compiled code stored inside the RDBMS, they generally execute faster uncompiled SQL statements (that normally have to be compiled each time a user or program calls them). Network traffic can also be significantly reduced because there is no need to send SQL statements as they are already compiled and stored on the server. Each individual statement is probably not large enough to improve the overall network performance, but, in a large system with thousands of users and tons of SQL statements, it can make a difference.

Database security

Stored procedures, functions, and triggers can be used for database security purposes. A stored procedure (or function) is a separate database object with its own database privileges. That means you can design a stored procedure in such way that it would, for example, update only certain columns; or insert rows with NULL values for columns that a user who executes the procedure has no permission to update — s/he would only need the privilege to execute that particular stored procedure. Triggers are even more useful for security implementation. For example, they can be designed in such way that certain actions performed by users on certain objects are written to special database tables or OS files. These records can be reviewed later.

Code reusability

Another important thing about stored routines is code reusability — once compiled, a stored procedure or user-defined function can be used over and over again by multiple users (or applications), saving time on retyping large SQL statements and reducing the probability of human errors. Also, when a persistent module needs to be changed, the change won't affect the client programs that access it, as long as all the calling parameters remain the same.

main elements of a SQL procedural language

• Variables and assignment
• Modularity, subprograms, and block structure
• Passing parameters
• Conditional execution (if else
• Repeated execution (loops WHILE, FOR))

# Stored procedures

Stored procedures are linear or sequential programs. The syntax varies from implementation to implementation, but some common features can be emphasized. Stored procedures can accept parameters and allow local variable declarations; they are structured and allow the use of submodules; also, they allow repeated and conditional statement execution.

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении покупке товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект - хранимую процедуру (stored procedure).

# User-Defined Functions

User-defined functions combine the advantages of stored procedures with the capabilities of SQL predefined functions. They can accept parameters, perform specific calculations based on data retrieved by one or more SELECT statement, and return results directly to the calling SQL statement.

BuiltIn: TRIM, CURRENT_DATE, LOWER, UPPER, ABS, CEIL, FLOOR, POWER(x, n), RAND, CONCAT

# Triggers

A trigger is a special type of stored procedure that fires off automatically whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.

Для создания триггера применяется выражение CREATE TRIGGER, после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция. Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON.

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

• AFTER: выполняется после выполнения действия. Определяется только для таблиц.
• INSTEAD OF: выполняется вместо действия (то есть по сути действие - добавление, изменение или удаление - вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT, UPDATE или DELETE. Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие. И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

# Cursors

Курсор - это объект базы данных, который позволяет приложениям работать с записями "по-одной", а не сразу с множеством, как это делается в обычных SQL командах.

Порядок работы с курсором такой:

• Определить курсор (DECLARE)
• Открыть курсор (OPEN) *Получить запись из курсора (FETCH)
• Обработать запись
• Закрыть курсор (CLOSE)

Cursor is a special programming construct that allows you to create a named working area and access its stored information. The main advantage of cursors is the ability to work with individual rows one-by-one rather than with the record set as a whole. For example, all DML statements work with record sets, so you could change "all or nothing". If your update statement is changing hundreds of thousands rows, just one single row could cause the whole statement to fail and roll back all the changes if it violates a column constraint, resulting in serious time losses. Fortunately, cursors are able to handle such situations working with each row individually. A logic that combines cursors, loops, and conditional statements could generate a warning, store the unsuccessfull row information in a special table, and continue processing. Also, cursors give you flexibility on commits and rollbacks (you can commit after each row, after ten rows, or after every five hundred rows), which sometimes can be very useful to save system memory space; you can employ conditional logic and perform calculations on certain values before they are used in your DML statements; using cursors, you are able to update multiple tables with the same values, and much more. Different RDBMS vendors implement cursors in different ways. Both syntax and functionality vary, which makes it difficult to talk about some generic cursor. SQL99 standards require a cursor to be scrollable, that is, you should be able to move back and forth from one record in the record set to another, but until recently only a few RDBMS vendors (notably MS SQL Server) implemented such functionality. The main reason is that a scrollable cursor is a huge resource waste and not every system can afford it. It is a known fact that many MS SQL Server developers are explicitly warned against using cursors unless it is absolutely necessary, whereas for PL/SQL programmers cursor use is an integral part of their everyday work. In spite of all the differences, all cursor implementations have some common features. In general, the main operations you can perform on a cursor are DECLARE, OPEN, FETCH, and CLOSE.

• DECLARE associates a cursor name with a certain SELECT statement and defines a memory structure to hold the appropriate columns (that could be of different data types)

• OPEN statement executes the underlying query and identifies the result set consisting of all rows that meet the conditions specified on the cursor declaration.

• FETCH retrieves the current row (for nonscrollable cursors) or a specific row (for scrollable cursors), parses the row, and puts the column values into predefined set of variables.

• CLOSE statement deallocates memory, releases locks, and makes the cursor's result set undefined.

Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими рядами (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является ряд. В этой ситуации на первый план выступает концепция курсора, и в таком контексте курсор – указатель на ряд.

Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор – запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора. Указанная область в памяти поименована и доступна для прикладных программ.

Обычно курсоры используются для выбора из базы данных некоторого подмножества хранимой в ней информации. В каждый момент времени прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах SQL, встроенных в написанные на языках процедурного типа прикладные программы. Некоторые из них неявно создаются сервером базы данных, в то время как другие определяются программистами.

В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия:

создание или объявление курсора; открытие курсора , т.е. наполнение его данными, которые сохраняются в многоуровневой памяти; выборка из курсора и изменение с его помощью строк данных; закрытие курсора, после чего он становится недоступным для пользовательских программ; освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память. В разных реализациях определение курсора может иметь некоторые отличия. Так, например, иногда разработчик должен явным образом освободить выделяемую для курсора память. После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование его имени. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом. Сразу после восстановления она становится доступной для других операций: открытие другого курсора и т.д.

В некоторых случаях применение курсора неизбежно. Однако по возможности этого следует избегать и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо того, что курсоры не позволяют проводить операции изменения над всем объемом данных, скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.

# Views

Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.

Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.

Для создания представления используется команда CREATE VIEW