arun-udayakumar

My Learnings in SQL

Arun Udayakumar's photo
Arun Udayakumar
·

4 min read

Table of contents

Start: sudo mysql

Database:

Create: CREATE DATABASE demo;

Create/Import from dump: sudo mysql < employees.sql

Show: SHOW DATABASES; or SHOW SCHEMAS;

Use DB: use db_name;

Delete: DROP DATABASE IF EXISTS demo;

Commands which require elevated rights:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW

Tables:

Show all Tables: SHOW TABLES;

Describe: DESC titles;

Rename Table: ALTER TABLE old_table RENAME TO new_table;

Delete a Table: DROP TABLE IF EXISTS example_table;

Help:

Example: HELP SELECT

Official site: https://dev.mysql.com/doc/refman/8.0/en/expressions.html

Query:

  1. Get 1st 6 rows from table:

     SELECT * FROM titles 
 LIMIT 6;

  2. Get last 6 rows from table

     SELECT * FROM (
     SELECT * FROM titles 
      ORDER BY emp_no DESC
      LIMIT 6
 ) subquery
 ORDER BY emp_no ASC;

  3. Count Distinct Values from a column

     SELECT COUNT(DISTINCT title)
 FROM titles;

  4. Get all employees first_name starting with Z

     SELECT * from employees 
 WHERE first_name REGEXP '^Z';

  5.  -- Perform left outer join
 SELECT
 employees.emp_no,
 employees.first_name 
 FROM employees 
 LEFT OUTER JOIN salaries 
 ON employees.emp_no=salaries.emp_no;

DDL - Data Definition Language

CREATE - create db or its objects (like table, index, function, views, store procedure and triggers)

DROP - to delete objects from the database

ALTER - alter the structure of the database

TRUNCATE - remove all records from a table, including

RENAME -

REGEX:

https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_editor

SELECT * from employees 
WHERE first_name REGEXP '^Z';

Numerical data types:

bigint - 64-bit representation of an integer. It takes up 8 bytes of storage. (long long int) 2^63

int -

smallint -

tinyint -

decimal(s, d) - 12.50 decimal(4,2)

Character Data types:

char(s) - 255 chars. If char(30) - 30 chars memory is blocked

varchar(s) - Here if varchar(30) and only given 3 chars then only 3 char memory will be used.

text 65535 chars

Date & Time Data Types:

date:

time:

Year:

Constraints:

**Not Null: ** Column cannot have null value

**Default: ** Default values will be assigned for a column

**Unique: ** All values are unique in column (Names should be unique)

**Primary Key: ** Not null + unique

Task:

  1. Name the table:

  2. Define the columns:

  3. Assign the data types of columns

CREATE DATABASE Company; //->to create Company database
use Company;
create table employee(
    e_id int not NULL,
    e_name VARCHAR(20),
    e_salary int,
    e_age int,
    e_gender VARCHAR(20),
    e_dept VARCHAR(20),

    PRIMARY KEY(e_id)
    );

Insert column data into SQL

    INSERT INTO employee values(
        1, 'Ashok', 456788, 25, 'Male', 'Operations'
    );

    INSERT INTO employee values(
        2, 'Raju', 366788, 23, 'Male', 'Store'
    );

    INSERT INTO employee values(
        3, 'Anitha', 1366788, 30, 'Female', 'R&D'
    );

Query the table and check the results

Extract employee names from the table

SELECT e_name from employee; //prints employee names

SELECT e_name,e_gender,e_salary from employee;

SELECT * from employee;

SELECT distinct e_gender from employee; //Like  for(auto s: set<string>)

Inner Join:

create table employee(
    e_id int not NULL,
    e_name VARCHAR(20),
    e_salary int,
    e_age int,
    e_gender VARCHAR(20),
    e_dept VARCHAR(20),

    PRIMARY KEY(e_id)
    );

    INSERT INTO employee values(
        1, 'Ashok', 456788, 25, 'Male', 'Operations'
    );

    INSERT INTO employee values(
        2, 'Raju', 366788, 23, 'Male', 'Store'
    );

    INSERT INTO employee values(
        3, 'Anitha', 1366788, 30, 'Female', 'R&D'
    );

    SELECT * from employee;



create table reference(

    india_state VARCHAR(30),
    e_id int not NULL,    
    country VARCHAR(30),

    PRIMARY KEY(e_id)
    );

INSERT INTO reference values(
    'Tamil Nadu', '3', 'India'
    );
INSERT INTO reference values(
    'Kerala', '2', 'India'
    );
INSERT INTO reference values(
    'Maharashtra', '1', 'India'
    );

SELECT * FROM employee;
SELECT * FROM reference;

SELECT employee.e_id, employee.e_name,reference.country,reference.india_state FROM employee
INNER JOIN reference ON employee.e_id=reference.e_id;

Some important Queries:

use Sandbox;
SELECT * from Sales.Orders WHERE orderid=10261

SELECT * from Sales.Orders WHERE shipcountry='USA'

SELECT COUNT(*) FROM Sales.Orders; //Gives row count 

//SELECT * from Sales.Orders WHERE orderdate=

select MAX(orderdate) from Sales.Orders

select min(orderdate) from Sales.Orders

select * from sales.Orders WHERE orderdate='2015-05-01'

SELECT * FROM Sales.Orders WHERE orderdate >='2016-01-01' AND orderdate <= '2016-12-31'

SELECT shipcountry FROM Sales.Orders;

SELECT DISTINCT shipcountry FROM Sales.Orders //Unique set of DATA

SELECT orderid,shipcountry FROM Sales.Orders order by shipcountry //Sorting

CREATE TABLE testtable (
    col1 char(10),
    col2 int
);

INSERT into testtable VALUES ('A', 1), ('D',2), ('F',6), ('D',6);

SELECT * from testtable;
SELECT * from testtable ORDER by col1 asc;
SELECT * from testtable ORDER by col1 desc;

// SELECT * from testtable ORDER by col1 ASC col2 DESC//wrong

drop table testtable
SQL