SQL.txt

=====================================================INTRODUCTION START=====================================================
!!sql isn't case sensitive
!!date, datetime, timestamp, time, blob, text data type required quote

!!ACCESSING
!!command: mysql -u root -p

!!help data manipulation - this command return all the command for DML
!!help data definition - this command return all the command for DDL
=====================================================INTRODUCTION END=====================================================

=====================================================DATA TYPE START=====================================================
!!INT
!!it holds whole number between -32768 and 32767
!!it can not hold a decimal number(fractional)
!!the number of digit may be specified in parenthesis: data_type (size)

!!DEC
!!it holds fixed point numbers
!!the total number of digit and the number of digit after decimal point specified in parenthesis: data_type (size, point)
!!decimal point(.) and negative sign(-) are not counted in size
!!the maximum size of decimal is 65 and for point is 30, default value for both is 0
!!command: column_name DEC(number, number)

!!CHAR
!!it holds a fixed length string
!!it can contain letters, numbers and special characters
!!it can store up to 255 character
!!length is defined in parenthesis

!!VARCHAR
!!it holds a variable length string
!!it can contain letters, numbers and special characters
!!the maximum length is defined in parenthesis
!!it can store up to 255 character
!!if we put greater value than 255, it will be converted to a TEXT type

!!TEXT
!!it holds a string with a maximum length of 65535 character

!!DATE
!!it displays Date values in yyyy-mm-dd format

!!DATETIME
!!it displays DATETIME values in yyyy-mm-dd hh:mm:ss format

!!TIMESTAMP
!!it displays current date time
=====================================================DATA TYPE END=====================================================

=====================================================DATABASE START=====================================================
!!CREATE
!!command: CREATE DATABASE databasename;
!!guidelines for databasename:
-> start with an alphabet
-> blank space and single quote are not allowed
-> reserve words of RDMS/DBMS cann't be used

!!DELETE - DROP
!!command: DROP DATABASE databasename;

!!SHOW CREATE DATABASE
!!it show the command which you have used while creating the database
!!command: SHOW CREATE DATABASE database_name;
!!bassically it return "CREATE DATABASE database_name"
!!it is used when we need to alter database and we don't want to write same command again
=====================================================DATABASE END=====================================================

=====================================================TABLE START=====================================================
!!CREATE
!!command: CREATE TABLE table_name (column_name_1 data_type (size) [constraints],column_name_2 data_type (size) [constraints]);
!!constraints: NOT NULL, PRIMARY KEY
!!make sure your are in correct database

!!DETAILS-DESCRIBE
!!command: DESC table_name;
!!it is onlyy describe the table
!!describe the structure of table not the information(row) inside table

!!SHOW COLUMNS
!!command: SHOW COLUMNS FROM table_name;
!!show all the columns of table and their data type
!!it just like DESC table_name;

!!REMOVE
!command: DROP TABLE table_name;(make sure you are in correct database)

!!RENAME
!!command: RENAME TABLE old_table_name to new_table_name;
!!rename one or more table

!!COPY - SAME DATABASE
!!command: CREATE TABLE new_table LIKE old_table;INSERT new_table SELECT * FROM old_table;

!!COPY - DIFFERENT DATABASE
!!use the database where you want to cpy the old table
!!command: CREATE TABLE new_table LIKE old_db.old_table;INSERT new_table SELECT * FROM old_db.old_table;

!!TRUNCATE TABLE
!!only delete the data inside table, not the table itself
!!it's like clearing all the table data
!!command: TRUNCATE TABLE table_name;

!!SHOW CREATE TABLE
!!it show the command which you have used while creating the TABLE
!!command: SHOW CREATE TABLE table_name;
!!bassically it return "CREATE TABLE table_name (column_name_1 data_type (size) [constraints],column_name_2 data_type (size) [constraints]);"
!!it is used when we need to alter table and we don't want to write same command again
=====================================================TABLE END=====================================================

=====================================================CRUD START=====================================================
!!READ-SELECTION
!!select with limit: SELECT * FROM table_name LIMIT numberOfRows/start-1,numberOfRows;(top->bottom)

!!DELETE-DELETION
!!command: DELETE FROM table_name WHERE column_name=value;
=====================================================CRUD END=====================================================

=====================================================COLUMN START=====================================================
!!REMOVE - DROP
!!command for without constraints: ALTER TABLE table_name DROP COLUMN column_name;
=====================================================COLUMN END=====================================================

=====================================================WHERE START=====================================================
!!operators: =,!=,>,<,>=,<=,BETWEEN(between a inclusive range),LIKE(search for a pattern),IN(to specify multiple possible values for a column)
!!EXPLORE THIS OPERATOR WITH STRING VS CHARACTER

!!NULL
!!it represent missing unknown data
!!NULL != 0

!!IS NULL
!!it is used to select only the records with NULL values in the column
!!command: WHERE column_name IS NULL;(no need use column_name=NULL)

!!IS NOT NULL
!!it is used to select only the records with no NULL values int the column
!!command: WHERE column_name IS NOT NULL;
=====================================================WHERE START=====================================================

=====================================================OPERATOR & CONSTRAINT START=====================================================
!!IN-NOT IN
!!command: WHERE name IN ('masum','billah')
!!normally, in where, at condition part we can put single value, but in allow to put multiple value
!!in return data which are matched, not in doesn't return data which are not matched

!!BETWEEN
!!it selects values within a range, it can be number, text, dates
!!command: WHERE column_name BETWEEN 5 AND 10;
!!for number it included both
!!for text it only include first one
!!command: WHERE column_name BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd';
!!it include both date

!!NOT BETWEEN
!!display data which is not in the range
!!command: WHERE column_name NOT BETWEEN 5/"begining_text" AND 10/"ending_text";
!!text include begining_text but not include ending_text

!!ORDER BY
!!command: SELECT * FROM table_name ORDER BY column_name DESC/ASC
!!DESC - descending order
!!ASC - ascending order

!!NOT NULL
!!by default a table's column can hold NULL values
!!NOT NULL constraint enforces a record to always contain a value
!!you cann't make changes without a value

!!GROUP BY
!!groups rows that have same values int summary rows
!!most of the time it used with function
!!it often used wit aggregate functions like count, max, min, sum, avg
!!when we use group by, we don't need where clause
!!command: SELECT emp_name, MIN(salary) FROM emp GROUP BY emp_name;
!!in the table multiple employee have same name(emp_name)
!!this command will group the table having the minimum salary of multiple employee with same name

!!HAVING
!!where clause cann't use with aggregate functions
!!so when we need grouping with certain condition we use having instead of where
!!it apply condition on a group made by group by
!!command: SELECT emp_name, MIN(salary) FROM emp GROUP BY emp_name HAVING MIN(salary)>2500;

!!LIKE
!!search for a specified pattern in a column
!!command: WHERE column_name LIKE 'pattern';

!!NOT LIKE
!!return everything except a specified pattern in a column

!!WILDCARDS
!!use to search for data within a table
!!these characters are used with the LIKE operator
!!%           - zero or more character
!!_           - one single character
!![charlist]  - sets and ranges of character
!![!charlist] - matches only a character NOT specified witin the brackets

!!WILDCARD - %
!!'Geek%' - All starting with geek
!!'%Geek' - All ending with geek
!!'%mid%' - All containing with mid

!!AUTO INCREMENT
!!generate an unique number, when a new record is inserted into a table
!!in a table there will be only one auto incremented column
!!it ignore NULL value
!!use for primary key
!!command: column_name int NOT NULL AUTO_INCREMENT
!!it's okay if you don't consider any value during insertion. if you put NULL, it will ignored. if you try put a value which is already exist, then new data will be not added, existing data will be keep as it is, new data will be erase
!!if set value manually, it will override auto incremented value 
!!its better to not set any size or set max size of auto incremented column
!!STARTING AUTO INCREMENT WITH A PARTICULAR NUMBER IN SQL
!!command: ALTER TABLE table_name AUTO_INCREMENT=starting_number;

!!DISTINCT
!!used to display unique values
!!command: SELECT DISTINCT column_name FROM table_name;
=====================================================OPERATOR & CONSTRAINT END=====================================================

=====================================================FUNCTION START=====================================================
!!MIN, MAX, SUM, AVG
!!applied on column(sum,avg - numeric column)
!!return a single value only

!!SQRT
!!it applied on column in each row

!!UPPER, LOWER
!!applied on column
!!command: SELECT UPPER(column_name) FROM table_name;

!!REVERSE
!!applied on column
!!command: SELECT REVERSE(column_name) FROM table_name;

!!NOW
!!it return the current system date and time
!!command: SELECT column_name, NOW() FROM table_name;

!!COUNT
!!returns the number of values of specified column
!!NULL values will not be counted
!!command: SELECT COUNT(column_name) FROM table_name
!!if column_name is equal (*=> COUNT(*)), then it return the number of record in a table
!!if column_name is DISTINCT, it return the number of distinct values of the specified column: COUNT(DISTINCT column_name)

!!CONCAT
!!it join column value
!!SELECT CONCAT(column_name_1,column_name_2)
!!SELECT CONCAT(column_name_1,' ',column_name_2)
=====================================================FUNCTION END=====================================================

=====================================================KEY START=====================================================
!!UNIQUE
!!uniquely identifies each record in database table
!!can contain NULL values, NULL can't consider as duplicate value(in one column, multiple row may contain NULL value but other value must be UNIQUE)
!!can be many UNIQUE constraint per table
!!UNIQUE constraint refers all the value of this column will be unique
!!each primary key is a unique key, not each unique is primary key
!!set it like other constraint: CREATE TABLE table_name (column_name_1 data_type (size) UNIQUE KEY);

!!PRIMARY
!!uniquely identifies each record in database table
!!must contain unique values
!!cann't contain NULL values
!!most table should have primary key
!!each table can have only ONE primary key
!!set it like other constraint: CREATE TABLE table_name (column_name_1 data_type (size) NOT NULL PRIMARY KEY);
!!set it at end: CREATE TABLE table_name (column_name_1 data_type (size) NOT NULL, column_name_2 data_type (size) PRIMARY KEY (column_name_1));

!!CANDIDATE
!!minmal set of attribute that uniquely identifies a record
!!it can contain null value but not duplicate value
!!each can have multiple CANDIDATE key in a table
!!subset of super key
!!single key is consider as candidate key, not a group of key

!!SUPER
!!a single key or a group of multiple key that uniquely identifies a record
!!group of key must contain one unique key
!!can contain redundant attribute(are not an element of candidate key) that might not be important for identifying tuples

!!FOREIGN
!!a FOREIGN KEY in on table points to a PRIMARY KEY in another table
!!if a column in current table, is primary key in another table then the column in current table is called foreign key
!!column name(foreign key name) can be different
!!foreign key value can be null, even though primary key value can't
!!foreign key don't have to be unique in fact, they often aren't
!!the data type of foreign key will be as same as the primary key, not only data type, each of the property of primary must implemented in foreign key
!!command: FOREIGN KEY (foreign_key_name) REFERENCES primary_key_holder_table_name(primary_key_holder_column_name)
!!during the key definition, the number of columns in the child table and parent table specified in the FOREIGN KEY and REFERENCES must be the same
!!DROM FOREIGN KEY
!!command: ALTER TABLE table_name DROP FOREIGN KEY constraint_name

!!COMPOSITE
!!A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table
!!command: PRIMARY KEY(column_name_1,column_name_2)(two different columns work as primary key, this typeof primary key is called composite key)
!!try to avoid composite key in real world programming
=====================================================KEY END=====================================================

=====================================================RELATION START=====================================================
!!CROSS JOIN - CARTESIAN PRODUCT
!!it returns every row from one table crossed with every row from the second
!!may return very large result-sets
!!each row of a table will pair with every row of another table
!!the joined table(used after join keyword) will lead the output
!!command: SELECT * FROM table_name CROSS JOIN table_name;
=====================================================RELATION END=====================================================