SQL is one of the widely used language for retriving and storing information in a relational database. SQL is not an acronym for anything but is usually known as it stands for Structured Query Language.
Codd's definition of relational model.
Proposed a language called DSL/ Alpha for manipulating data in relational table.
IBM worked on Codd's ideas and simplified DSL/A - SEQUEL - SQL - 40 years old.
Most popularly known Open Source Database Management Systems are 1) Post greSQL 2) MYSQL
Apache Drill is an Open Source Query Engine that allows to query data in varied sources.
A series of keyword forming an instruction is called a Statement / Query in SQL. Therefore you write a SQL Statement / SQL Query.
USE <database_name>
now, no need to use db name again.
now() - returns current date and time.
MySQL Datatypes:
- Character data - fix | variable length
- fix - right padded with spaces - not padded
- consumes same no. of bytes = different bytes
- Char(20)
- Varchar(20) - Max length is 65,535 bytes
- Numeric - int (4B bytes) | Bigint 0 - 2^64-1
- float(p,s) - 3.40 e +38
- Temporal data - related to dates and time
- Type - default format
- date YYYY-MM-DD
- datetime - YYYY-MM-DD HH:MI:SS
- timestamp - auto populates
How to create a Table in SQL Database?
Step 1 - Design - think, name of columns and their data types
Step 2 - Refinement - concept of normalization - ensures no duplicate or compound columns.
Step 3 - Building SQL Schema statements - CREATE table <table_name>
(<col_name datatype(length), col2_name datatype(len));
PRIMARY KEY - You need to tell server what column will serve as primary_key
add constriants to a table definition
CONSTRAINT <cons_name> TYPE (cname)
CONSTRAINT fk_c_name FOREIGN KEY (person_id) REFERENCES person (person_id)
SQL - schemea statements (not much discussion) transaction statement - being, end, rollback transactions
Data statement
All database elements created through SQL schema statements are stored in a special set of tables called data dictionary.
Data about data - Metadata
You can query data dictionary tables.
Optimizer
SQL Code - goes through the code
Execution plan - decides most efficient execution path.
How to think of a query?
Determine which table(s) you will need and then add FROM clause. Add conditions "WHERE" clause to filter out columns that you don't require.
Next, determine which columns you require from other tables and add them to your "SELECT" clause.
SELECT
SELECT pid, fname from <table name>
SELECT column names FROM <table_name> WHERE lname = 'turn';
Steps -
1. First to write, last to be evaluated.
2. Which of all possible clumns should be included in the result set.
3. You can alter/ process the values by using : literals, expressions, built-in functions, user-defined function calls
SELECT language_id * 5, upper(name), l_name from <table_name>
l_name is column alias - Use AS l_name for improved readability.
T-SQL
Data definition language
Create
Alter
Modifies an existing database - object
Drop
deletes an entire table, a view of a table or other objects in the database.
DML - Data manipulation commands
SELECT - retrives the certain records
INSERT - Insert into Product (product_cd, name) Values ('cd';'B.tech');
UPDATE - modifies record
Example query - UPDATE person SET street - '1225', city='Boston',State = 'Miami' WHERE person_id = '0213'; ### if WHERE is not included, all rows will get updated.
UPDATE product SET name ='MBA' WHERE name = 'B.TEch';
DELETES - deletes records
Example query - DELETE FROM <table_name> WHERE person_id = 2;
DROP -
DROP TABLE <table_name>
Primary Key is used for isolating the row of interest (uniquely).
Command and description
GRANT - Gives a priviledge to user
REVOKE - Take back priviledges
Table Alias - While joining tables you need to identify which table you are referring to - either use full name or assign an alias.
FROM <table_name> AS <alias>
ALTER TABLE -
ALTER TABLE person MODIFY col_name datatype attributes;
DUPLICATES - REMOVE
SELECT <column_name> from <t_name> ORDER BY c_name;
same ids repeated - to pull distinct set of values.
SELECT DISTINCT <col_name> from t_name ORDER BY col_name;
POTENTIAL ISSUES -
- Non-unique primary key
- Non-existent foreign key
- Column value violations
- Invalid date conversions
THEORY -
1) Show tables;
QUERY CLAUSES -
1) SELECT
2) FROM
3) WHERE
4) GROUP BY
5) HAVING
6) ORDER BY
Where - when you filter out rows that you don't want
SELECT title from film WHERE rating = 'G' OR name ='A';
When you want to find trends in your data - use GROUP BY or HAVING
GROUP BY - group data by column values
SELECT row1, row2, count(*) FROM table t INNER JOIN rental r ON t.f_name = r.f_name GROUP BY t.F_name, r.f_name HAVING count(*) >=40;
FROM - tables used by query , along with the means of linking the table together.
Tables - permanent - create <table_name> , derived - sub query in-mem, temporary - volatile in memory and virtual - views
VIEWS -
Query that is stored in the data dictionary
Looks and acts like a table
CREATE VIEW <view_name> AS SELECT cust_id,l_name FROM customer;
SELECT f_name, l_name FROM <view_name> WHERE active = 0 ;
Views are created to hide columns from users and to simplify the complex database designs.
ORDER BY -
By default, results are not sorted in order.
order by is the mechanism for sorting your results using either raw column data or expresion based on a volumn data.
SELECT col1, col2 FROM customer c INNER JOIN rental r ON c.customer_id = r r.cust_id; WHERE date (r.rental_date) = '2005-06-14'; ORDER BY c.last_name;c.first_name (extending query)
- Also has a LIMIT clause to show top N results
- Sort by numeric placeholder
- Position in SELECT query
- desc - for descending order/
Frequently asked questions/ Inteview questions about SQL -
- How to duplicate a table in SQL?
