SQL is not 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.
Common open source db servers - 1) Post greSQL 2) MYSQL
Apache drill - open source query engine that allows to query data in varied sources.
now, no need to use db name again.
now() - returns current date and time.
1. 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 - 255 bytes
varchar - 65,535 bytes
others - medium text and long text
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
datetime - YYYY-MM-DD HH:MI:SS
timestamp - auto populates
Steps to create a table -
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
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.
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 pid, fname from <table name>
SELECT column names FROM <table_name> WHERE lname = 'turn';
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.
Data definition language
Modifies an existing database - object
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 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
1) Show tables;
QUERY CLAUSES -
4) GROUP BY
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
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?