Redirecting to
NADOS


  Prev   Next
1. CREATE: Create statement is used to create database or table. a. Database: Syntax: CREATE DATABASE DATABASE_NAME e.g. create database demo; Create database: sql_create_db b. use database: use statement is used to use the database. syntax: USE DATABASE_NAME e.g. use demo; c. Table: Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME1 DATATYPE, COLUMN_NAME2 DATATYPE...); e.g. create table employee (id int (10) primary key, name varchar (20) not null, age int (20), salary int (100)); Create table: sql_create_table 2. SELECT: Select statement is used to select data from a database. Syntax: SELECT * FROM TABLE_NAME; OR SELECT COLUMN_NAME1, COLUMN_NAME2, ... FROM TABLE_NAME; e.g. SELECT * FROM demo.employee; OR SELECT * FROM employee; select_star_query SELECT id, name FROM demo.employee; select_attribute_query Select clause: sql_select Select distinct: sql_distinct Select top: sql_top 3. INSERT INTO: Insert statement is used to insert new records in a table. Syntax: INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, ...) VALUES (VALUE1, VALUE2, ...); OR INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, ...); //this statement requires values to be inserted in the order or column e.g. insert into employee (id, name, age, salary) values (2, 'anjali', 20, 200000); OR insert into employee values (1,'priya',18,180000); select * from employee; insert_query Insert into clause: sql_insert 4. WHERE: It is used to filter the records. Syntax: SELECT * FROM TABLE_NAME WHERE CONDITION; e.g. sql_table select * from employee where age = 18; where_query Where clause: sql_where Where clause with AND/OR/NOT: sql_and_or.asp 5. ORDER BY: It is used to sort the fetched data either in ascending or descending order based on one or more column. Syntax: Ascending order: SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME; OR SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME ASC; Descending order: SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME DESC; e.g. in ascending order: select * from employee order by id; OR select * from employee order by id asc; sql_table select * from employee order by id desc; order_by_desc Order by clause: sql_orderby 6. IS NULL : sql_null_values table_with_null_values e.g. SELECT * FROM EMPLOYEE WHERE AGE IS NULL; is_null_query 7. UPDATE: It is used to modify the existing records in a table. e.g. UPDATE EMPLOYEE SET NAME = 'ANAMIKA' WHERE ID = 1; update Update clause: sql_update 8. DELETE: It is used to delete the existing records in a table. e.g. DELETE FROM EMPLOYEE WHERE ID=6; //tuple with id = 6 is deleted from employee table delete clause: sql_delete 9. AGGREGATE FUNCTIONS: Aggregate functions are used to perform calculations on multiple rows of a single column of a table and returns single value as output. Aggregate functions are count, sum, avg, min, max. Aggregate functions: a. aggregate-functions b. sql_count_avg_sum c. sql_min_max d. sql_alias a. COUNT (): It returns the number of rows that satisfies the condition. e.g. select count (*) from employee //it returns the number of rows in a table count_star select count (*) as Age_equals_18 from employee where age = 18; count_star_condition //resultant column name renamed as Age_equals_18 b. AVG (): It returns the average value of a numeric column. e.g. select avg(age) from employee; avg select avg(age) as average_of_age_18 from employee where age = 18; avg_condition c. SUM (): It returns the sum of numeric value. Eg. select sum(age) as sum_of_age from employee sum_query d. MAX (): It returns the maximum value of the selected column. select max(salary) from employee max_query Que: find employee's detail having maximum salary: Nested query: query inside another query. SELECT * FROM EMPLOYEE WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE); max_salary e. MIN (): It returns the minimum value of the selected column; e.g. select min(salary) from employee; 10. LIKE: It is used to search a particular pattern in a table. It is used with where clause. It uses some wildcard characters like %, _ , $, * etc. Wild Card characters: sql_wildcard_characters e.g. SELECT * FROM EMPLOYEE WHERE NAME LIKE '%AN%'; //it selects all the name having 'an' as substring. like_query Like operator: sql_like 11. IN: In operator is a shorthand for multiple 'or' conditions. e.g. with multiple OR conditions: SELECT * FROM EMPLOYEE WHERE AGE=18 OR AGE= 20; Now shorthand query for above query is: SELECT * FROM EMPLOYEE WHERE AGE IN (18,20); IN operator: sql_in 12. BETWEEN: It selects values in the given range. e.g. SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 18 AND 20; //selects all employees with age between 18 and 20 between and not between: sql_between_and_not_between 13. ALIASES: It is used to give temporary name to table or column using 'as' keyword. e.g. SELECT NAME AS EMPLOYEE_NAME FROM EMPLOYEE; Aliases: sql_alias_w3school sql_alias_geeks 14. JOINS: Joins: sql_join Inner, left, right, full join: inner-left-right-and-full-joins a. Inner Join: sql_join_inner Video on Join or Inner Join: Join or Inner Join Join or Inner Join with 'ON' operation: Join or Inner Join with 'On' operation b. Left Join: It returns all records from the left relation and matched records from the right relation and if there is no match then the result is null from the right side. left_join sql_join_left c. Right join: It returns all records from the right table and matched records from the left table and if there is no match then the result is null from the left side. right_join sql_join_right d. Full join: It returns all matching records from both tables whether the other matches or not. full_join sql_join_full Video on Full Outer Join: Full Outer Join e. Self-join: sql_join_self sql-join-cartesian-join-self-join f. Natural Join: Video on Natural Join Natural Join A Natural Join is a join operation in which 2 tables are joined based on all common columns. natural_join 15. UNION: sql_union sql-union-clause 16. GROUP BY: It is used to group the tuples that have same value with the help of some functions. e.g. SELECT AGE FROM EMPLOYEE GROUP BY AGE; sql-group-by 17. HAVING: sql_having 18. DROP DATABASE: It is used to drop an existing database. Syntax: DROP DATABASE DATABASE_NAME; e.g. drop database demo; Drop database: sql_drop_db 19. DROP TABLE: It is used to drop an existing table. Syntax: DROP TABLE TABLE_NAME; e.g. drop table employee; drop table: sql_drop_table 20. TRUNCATE TABLE: It is used to delete all the data inside the table but not the structure of table. Syntax: TRUNCATE TABLE TABLE_NAME; e.g. truncate table employee; truncate table: sql_drop_table 21. ALTER TABLE: It is used to add, delete and modify an existing column. Add column in an existing table: Syntax: ALTER TABLE TABLE_NAME ADD COLUMN DATATYPE; e.g. alter table employee add column department varchar (20) not null; modify column in an existing table: syntax: ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME DATATYPE; drop column in an existing table: syntax: ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME; alter table: sql_alter 22. Constraints: sql_constraints 23. View: sql_view 24. Stored Procedures: sql_stored_procedures 25. Sql Injection: sql_injection 26. MySQL Functions: sql_ref_mysql REFERENCES: SQL Tutorial (w3school): sql-tutorial_w3school SQL Tutorial (geeksforgeek): sql-tutorial_geeks
 
Run
Id Name