The SQL beginner learning handbook for people who want to learn about DBMS, RDBMS and SQL commands and how to generic SQL commands in day to day life.
1. History and Intro RDBMS
1.1 History of Databases
IBM developed a prototype relational database model as early as 1974 called ‘System R’. IBM’s research into relational databases had come to the attention of a group of engineers in California. They were so convinced of the potential that they formed a company called “Relational Software, Inc”.in 1977 to build such a database. Their product was called Oracle and the first version for RDBMS was released in 1979, thereby becoming the first commercial RDBMS, beating IBM to market by 2 years.
In the 1980s the company was renamed Oracle Corporation and throughout the 1980s, new features were added to the database and performance improved as the price of hardware came down and Oracle became the largest independent RDBMS vendor. By1985 they boasted of having more than 1000 installations.
1.2 About Relational Databases
Every organization has information that it must store and manage to meet its requirements. For example, a corporation must collect and maintain human resources records for its employees. This information must be available to those who need it. An information system is a formal system used for storing and processing, retrieval of information.
An information system could be a set of cardboard boxes containing manila folders along with rules for how to store and retrieve the folders. However, most companies today use a database to automate their information systems. A database is an organized collection of information treated as a unit. The purpose of a database is to collect, store, and retrieve related information for use by database applications.
1.3 What is Database Management System (DBMS)?
A database management system (DBMS) is software that controls the storage, organization, and retrieval of data. Typically, a DBMS has the following elements:
- Kernel code — This code manages memory and storage for the DBMS.
- Repository of metadata — This repository is usually called a data dictionary. (A read-only collection of database tables and views containing reference information about the database, its structures, and its users.)
- Query language — This language enables applications to access the data. A database application is a software program that interacts with a database to access and manipulate data.
1.4 Relational Database Management System (RDBMS)
The relational model is the basis for a relational database management system (RDBMS). Essentially, an RDBMS moves data into a database, stores the data, and retrieves it so that it can be manipulated by applications. An RDBMS distinguishes between the following types of operations:
- Logical operations — In this case, an application specifies what content is required. For example, an application requests an employee name or adds an employee record to a table.
- Physical operations — In this case, the RDBMS determines how things should be done and carries out the operation. For example, after an application queries a table, the database may use an index to find the requested rows, read the data into memory, and perform many other steps before returning a result to the user. The RDBMS stores and retrieves data so that physical operations are transparent to database applications.
2. Structured Query Language (SQL)
- SQL is an English like language that is used to extract information RDBMS. They also have features to manipulate and define data in RDBMS.
- SQL is not a programming language but is a language that utilizes the concepts of SET theory (Venn diagrams).
- Develop by IBM in mid-1970. Today SQL is a common standard referred by all the RDBMS Oracle 11g add to SQL 2003 produced by ANSI SQL.
NOTE: All the code snipper screenshots are taken by myself so the Image credit goes to Dilan Jayasekara
2.1 SQL Components
SQL is a database language, based on relational principles, with commands for manipulating data.
SQL has 3 language components:
- Data Definition Language (DDL)
- Data Control Language (DCL)
- Data Manipulation Language (DML)
2.2 DDL
Commands that define a database, including creating, altering, and dropping tables and establishing constraints.
- Privileged users
- Used to maintain Database structure
- Define tables & integrity rules
Eg: create, index, alter and drop tables and views. Define keys, data items, data types and relationships
CREATE TABLE [This Table] ([Forename] TEXT, [Surname] TEXT);Creates a new table called This Table with two Text fields.- CREATE TABLE — creates a table with specified columns
- CONSTRAINT — specifies primary key, foreign key or other constraints
- CREATE VIEW — creates a view from one or more base tables
- CREATE SYNONYM — creates an alternative name for a table or view
- CREATE INDEX — creates an index on a table
- ALTER TABLE — adds or modifies columns in an existing table
- DROP TABLE / INDEX / VIEW / SYNONYM — removes a table, index, view or synonym from the
2.3 DCL
Commands that maintain and query a database:
- Used by Database Administrator for general control over the database
- Access control — grant and revoke privileges
- Transaction control — control changes made by DML commands to ensure consistency
GRANT SELECT ON table TO user;Allows a specified user to read (but not change) data in a specified table.Commands that control a database, including administering privileges and committing data:
- GRANT — assigns authorizations & privileges
- REVOKE — removes authorizations & privileges
- COMMIT — stores changes permanently in the database
- ROLLBACK — restores the database to the state after the last COMMIT
2.4 DML
Used for inserting, modifying, deleting and querying data values
SELECT [Forename], [Surname] FROM [Employees] WHERE [Surname] LIKE “B%”;Get the forename and surname from the Employees table for all rows where the employee’s surname begins with B.- SELECT — retrieves rows from a table
- INSERT — adds new rows to a table
- UPDATE — changes existing rows in a table
- DELETE — removes rows from a table
This was built into DBMS programming language
Also, Inserted into a program written in a general-purpose programming language (GPPL)
- C, VB, JAVA, etc.
- program pre-processed to create compiled procedures
Converted into procedure/function calls by a programmer and inserted into the GPPL.
4. Key SQL Commands and Keywords
4.1 Describe
Syntax
DESC[RIBE] {[schema.]object[@db_link]}Lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.
Terms
Schema — Represents the schema where the object resides. If you omit schema, SQL*Plus assumes you own object.
Object — Represents the table, view, type, procedure, function, package or synonym you wish to describe.
Example: DESCRIBE Hr.Employee;4.2 CREATE TABLE
4.3 ALTER TABLE
4.4 INSERT
4.5 DELETE
Question: How would I write an Oracle DELETE statement to delete all records in Table A whose data in field 1 & field 2 DO NOT match the data in field x & field z of Table B?
Answer: You could try something like this for your DELETE statement
4.6 UPDATE
4.7 SELECT/WHERE/AND/OR/NOT IN/ORDER BY
4.8 Drop/Truncate Table
Truncate
TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse). … Typically, TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table.
4.9 BETWEEN/COUNT
4.10 EXISTS
The Oracle EXISTS condition is considered “to be met” if the subquery returns at least one row. It can be used in SELECT, INSERT, UPDATE, or DELETE statement.
4.11 DISTINCT CLAUSE
The Oracle DISTINCT clause is used to remove duplicates from the result set. The DISTINCT Clause can only be used with SELECT statements.
4.12 LIKE
Using the SQL % Wildcard.
•The following SQL statement selects all customers with a City starting with “ber”:SELECT * FROM Customers
WHERE City LIKE ‘ber%’;-------------------------------------------------------------•The following SQL statement selects all customers with a City containing the pattern “es”:SELECT * FROM Customers
WHERE City LIKE ‘%es%’;-------------------------------------------------------------•The following SQL statement selects all customers with a City starting with any character, followed by "erlin":SELECT * FROM Customers
WHERE City LIKE '_erlin';-------------------------------------------------------------•The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":SELECT * FROM Customers
WHERE City LIKE 'L_n_on';------------------------------------------------------------------•The following SQL statement selects all customers with a City starting with "b", "s", or "p":SELECT * FROM Customers
WHERE City LIKE '[bsp]%';------------------------------------------------------------------•The following SQL statement selects all customers with a City ending with "a", "b", or "c":SELECT * FROM Customers
WHERE City LIKE ‘%[a-c]';-------------------------------------------------------------------4.13 IS NULL
The Oracle IS NULL condition is used to test for a NULL value. You can use the Oracle IS NULL condition in either a SQL statement or in a block of PLSQL code.
4.14 ALIAS
The ALIAS keyword allows a TABLE or COLUMN to be given a “shorthand” name in an SQL statement. This short name is referred to as an alias. Aliases are especially helpful if the SQL statement is complex or the table or column names are long
SELECT column_name AS “alias“SELECT column_name “alias“SELECT * from MY_USER as “newalias”5. Aggregate Functions
The Oracle/PLSQL MAX function returns the maximum value of an expression.
SELECT department, MAX(salary) as “Highest salary” FROM employees GROUP BY department;The Oracle/PLSQL AVG function returns the average value of an expression.
SELECT AVG(salary) as “Avg Salary” FROM employees WHERE salary > 25000;The Oracle/PLSQL MIN function returns the minimum value of an expression.
SELECT department, MIN(salary) as “Lowest salary” FROM employees GROUP BY department;The Oracle/PLSQL SUM function returns the summed value of an expression.
SELECT SUM(DISTINCT salary) as “Total Salary” FROM employees WHERE salary > 50000;
6. JOINS
Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.
There are 4 different types of Oracle joins:
- Oracle INNER JOIN (or sometimes called a simple join)
- Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
- Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
- Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)
6.1 ORACLE INNER JOIN (SIMPLE JOIN)
Chances are, you’ve already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.
6.2 ORACLE LEFT OUTER JOIN
Another type of join is called an Oracle OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
6.3 ORACLE RIGHT OUTER JOIN
Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
6.4 ORACLE FULL OUTER JOIN
Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in a place where the join condition is not met.
7. GROUP BY CLAUSE
The Oracle GROUP BY Clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
8. HAVING CLAUSE
The Oracle HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose condition is TRUE.
9. INTEGRITY CONSTRAINTS
Business rules specify conditions and relationships that must always be true or must always be false. For example, each company defines its own policies about salaries, employee numbers, inventory tracking, and so on. It is important that data maintain data integrity (accuracy and consistency), which is adherence to these rules, as determined by the database administrator or application developer.
- Primary key
- Unique Key
- Foreign Key
- Candidate Key
- Not Null
- Check
- Default
- Domain
Let’s take each from the above list and explain briefly.
9.1 Primary key
In a primary key constraint, the values in the group of one or more columns subject to the constraint uniquely identify the row. Each table can have one primary key, which in effect names the row and ensures that no duplicate rows exist.
A primary key can be natural or surrogate. A natural key is a meaningful identifier made of existing attributes in a table. For example, a natural key could be a postal code in a lookup table. In contrast, a surrogate key is a system-generated incrementing identifier that ensures uniqueness within a table. Typically, surrogate keys are generated by a sequence.
The Oracle Database implementation of the primary key constraint guarantees that the following statements are true:
–No two rows have duplicate values in the specified column or set of columns.
–The primary key columns do not allow nulls.
A typical situation calling for a primary key is the numeric identifier for an employee. Each employee must have a unique ID. An employee must be described by one and only one row in the employee’s table.
9.2 Unique Key
Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
9.3 Foreign Key
Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key.
9.4 Candidate Key
In a relational database, a unique key or a primary key is a candidate that uniquely identifies each row in a table.
9.5 Not Null
Allows or disallows inserts or updates of rows containing null in a specified column.
9.6 Check
A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.
The chief benefit of check constraints is the ability to enforce very specific integrity rules. For example, you could use check constraints to enforce the following rules in the hr.employees table:
–The salary column must not have a value greater than 10000.
–The commission column must have a value that is not greater than the salary.
The following example creates a maximum salary constraint on employees and demonstrates what happens when a statement attempts to insert a row containing a salary that exceeds the maximum:
9.7 Default
Default values are only used when a column has no value specified.
9.8 Domain
Each column contains the same type of data thus when you select a data type for a particular domain then DBMS will not accept any value of other data type.
Thank you very much for staying with me till the end of the article. Please check out these two Database/Data Science Articles that I wrote a while ago.
0 Comments