SQL Basics - Starting the SQL Journey

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?
  •  

 

Leave a comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.