Understanding SQL Basics for Data Analytics

DATA ANALYTICS SQL BASICS SQL BASICS

DML DML DATA MANIPULATION LANGUAGE DATA MANIPULATION LANGUAGE DMLS ARE USED TO MANIPULATE OR ACCESS DATA OF ANY DATABASE OBJECT. ARE USED TO MANIPULATE OR ACCESS DATA OF ANY DATABASE OBJECT.

DML DML KEYWORD #1 KEYWORD #1 - - INSERT INSERT 2 TYPES OF INSERT STATEMENTS ARE AVAILABLE : 2 TYPES OF INSERT STATEMENTS ARE AVAILABLE :- - 1. INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); 2. INSERT INTO table_name VALUES (value1, value2, value3, ...); If you are adding values for all the columns of the table, you do not need to specify the If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. same order as the columns in the table.

SAMPLE SQL CODE SNIPPET SAMPLE SQL CODE SNIPPET insert into employee121 values(101, 'Adam', 3000); insert into employee121 values(101, 'Adam', 3000); insert into employee121 ( insert into employee121 (empid,ename empid,ename) values (102, 'Nick'); ) values (102, 'Nick');

DML DML KEYWORD #2 KEYWORD #2 - - SELECT SELECT SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: % - The percent sign represents one, or multiple characters _ - The underscore represents a single character LIKE Syntax SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

DML DML KEYWORD #2 KEYWORD #2 - - SELECT SELECT SQL Wildcard Characters A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

DML DML KEYWORD #2 KEYWORD #2 - - SELECT SELECT The SQL IN Operator The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. IN Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT); SELECT * FROM INVOICE_BACKUP WHERE SELECT * FROM INVOICE_BACKUP WHERE CustomerId CustomerId =40; =40; GET THE DETAILS OF ALL CUSTOMER WHOSE BILLINGCITY IS EITHER LONDON OR PARIS. GET THE DETAILS OF ALL CUSTOMER WHOSE BILLINGCITY IS EITHER LONDON OR PARIS. SELECT * FROM INVOICE_BACKUP WHERE SELECT * FROM INVOICE_BACKUP WHERE BillingCity BillingCity IN (' IN ('London','Paris London','Paris'); '); FETCH THE TOTAL_AMT FOR CUSTOMERS WHOSE FETCH THE TOTAL_AMT FOR CUSTOMERS WHOSE BillingCity BillingCity IN (' IN ('London','Paris London','Paris'); '); SELECT CUSTOMERID,SUM(Total ) FROM INVOICE_BACKUP WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM INVOICE_BACKUP WHERE SELECT CUSTOMERID,SUM(Total ) FROM INVOICE_BACKUP WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM INVOICE_BACKUP WHERE BillingCity (' ('London','Paris London','Paris')) GROUP BY CUSTOMERID; ')) GROUP BY CUSTOMERID; BillingCity IN IN

DML DML - - SELECT SELECT SQL SQL - - BETWEEN BETWEEN The SQL BETWEEN Operator The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. BETWEEN Syntax SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

DML DML - - SELECT SELECT SQL Aliases SQL Aliases SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query. Alias Column Syntax Alias Column Syntax SELECT column_name AS alias_name FROM table_name; Alias Table Syntax Alias Table Syntax SELECT column_name(s) FROM table_name AS alias_name;

DML DML - - SELECT SELECT DISTINCT Statement The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT Syntax SELECT DISTINCT column1, column2, ... FROM table_name;

DML DML - - SELECT SELECT WHERE Clause The SQL WHERE Clause The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition. WHERE Syntax SELECT column1, column2, ... FROM table_name WHERE condition; text Fields vs. Numeric Fields SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:

DML DML SQL AND, OR and NOT Operators The SQL AND, OR and NOT Operators The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE. The NOT operator displays a record if the condition(s) is NOT TRUE.

DML DML SQL AND, OR and NOT Operators AND Syntax SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; OR Syntax SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; NOT Syntax SELECT column1, column2, ... FROM table_name WHERE NOT condition;

ANY / ALL SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (90,100); SELECT * FROM EMPLOYEES WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 100); SELECT * FROM EMPLOYEES WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 100);

DML DML UPDATE UPDATE UPDATE STATEMENT SYNTAX TO UPDATE FEW RECORDS: UPDATE STATEMENT SYNTAX TO UPDATE FEW RECORDS:- - UPDATE EMPLOYEE121 SET SALARY =3500 WHERE ENAME=101 UPDATE STATEMENT SYNTAX TO UPDATE ALL RECORDS: UPDATE STATEMENT SYNTAX TO UPDATE ALL RECORDS:- - UPDATE EMPLOYEE121 SET ENAME = 'ADAM'