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.



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';

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. 




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 person MODIFY col_name datatype attributes;




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;



  • Non-unique primary key
  • Non-existent foreign key
  • Column value violations
  • Invalid date conversions



1) Show tables;





2) FROM 





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. 


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.