ALL IN ONE (MYSQL)
1. Data
-> It is consider as the information that we can use to perform some kind of processing .
2. DATABASE
-> Database is consider as collection of (related) Multiple Data . database contents only the related data.
3. DBMS ( DataBase Management System )
-> DBMS is consider as the software which is use to manage the DataBase .
4. FIELD OR ENTITY
-> It is consider as value in a single column of a table .
Ex : likes RID , Nmae , Age , City .
5. RECORD
-> Record is consider as collection of multiple field/entity .
6. SQL (Structure Query Language )
-> It is a language which are is use to perform the manipulation on the Databse. This language is only understanable by the DBMS.
7. MYSQL
-> It is a DBMS software which manage Relational Database.
-> It is open source .
-> Its support almost every programnming language and operating system.
8. RDBMS (Relational Database Management System )
-> RBDMS is BDMS which works on the relational Database which contains multiple table which are related in Nature .
9. Show databases
-> This Query Display all databases which available on machine.
10. Create Database :_
-> By using this query we can create a new Database .
Query : create database database_Name ;
11. Use Database
-> By using this query we can select database which is already created or available in Database.
Query : use database_Name ;
12.SHOW TABLES
-> Show all the tables in the default database.
Query : show tables;
13. DESCRIBE|DESC tableName
-> Describe the details for a table . By using this query we can describe the schema of the table . Describe the schema of the table. That schema content the Name , column , its datatype , its constraint .
Query : Desc table_Name ; OR Describe table_Name ;
14. create Table Query
-> By using there query we can create new table in selected Database. for the create table query we have ti specify the name column ,its datatype, constraite(if any).
Query : create table table_Name (
column1 datatype,
column2 datatype,
);
15. Select Query
-> By using the select Query we can fetch the data from table for specific condition .
Query : select * from table_Name ;
16. Insert Query
-> By using query we can insert new record into the table .
Query : INSERT INTO table_name VALUES (value1, value2, value3, …) ;
-- Insert a row with all the column values
Ex : INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
-- Insert multiple rows in one command
Ex : INSERT INTO products (productCode, name, quantity, price) VALUES
('PEC', 'Pencil 2B', 10000, 0.48),
('PEC', 'Pencil 2H', 8000, 0.49);
17. Select
-> select specific column from table .
-> when we specify the star (*) operation with the select query get all the column gets display but if we want the specific column then we can mention its column name.
Query : SELECT * FROM tableName ;
Ex :
-- List all rows for the specified columns
SELECT name, price FROM products;
18. Where Clause
-> we can use the WHERE clause with select query to filter out specific record from the table & display only such a record which we want to be get according to our condition .
Query : SELECT * FROM tableName Where Condition ;
Ex :
-> SELECT name, price FROM products WHERE price < 1.0;
19. Comparison Operators
-> For numbers (INT, DECIMAL, FLOAT), you could use comparison operators: '=' (equal to), '<>' or '!=' (not equal to), '>' (greater
than), '<' (less than), '>=' (greater than or equal to), '<=' (less than or equal to), to compare two numbers.
For example, price >
1.0, quantity <= 500.
20. Arithmetic Operators
-> we can perform arithmetic operations on numeric fields using arithmetic operators, as tabulated below .
Operator Describtion
+ Addition
- Subtraction
* Multiplication
/ Division
DIV Integer Division
% Modulus (Remainder)
21. Logical Operators - AND(&&), OR(||), NOT, XOR
Ex :
-> SELECT * FROM products WHERE quantity >= 5000 AND name='sam';
-> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');
21. IN, NOT IN
You can select from members of a set with IN (or NOT IN) operator. This is easier and clearer than the equivalent AND-OR expression.
Ex :
-> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');
22. BETWEEN, NOT BETWEEN
-> To check if the value is within a range, you could use BETWEEN ... AND ... operator. Again, this is easier and clearer than the
equivalent AND-OR expression.
-> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
23. String Pattern Matching - LIKE and NOT LIKE
-> For strings, in addition to full matching using operators like '=' and '<>', we can perform pattern matching using operator LIKE (or
NOT LIKE) with wildcard characters. The wildcard '_' matches any single character; '%' matches any number of characters (including
zero).
For example,
'abc%' matches strings beginning with 'abc';
'%xyz' matches strings ending with 'xyz';
'%aaa%' matches strings containing 'aaa';
'___' matches strings containing exactly three characters; and
'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more
characters.
-- "name" begins with 'PENCIL'
-> SELECT name, price FROM products WHERE name LIKE 'PENCIL%';
-- "name" begins with 'P', followed by any two characters,
-- followed by space, followed by zero or more characters
-> SELECT name, price FROM products WHERE name LIKE 'P__ %';
24. ORDER BY Clause
-> By using the order by clause we display the data using sorted by the order by cluase only display the data but physical table is not affected .
Query : SELECT ... FROM tableName
WHERE criteria
ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...
Ex :
-- Order the results by price in descending order
-> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;
-- Order by price in descending order, followed by quantity in ascending (default) order
-> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity;
25. LIMIT Clause
-> A SELECT query on a large database may produce many rows. You could use the LIMIT clause to limit the number of rows displayed,
Ex :
-- Display the first two rows
-> SELECT * FROM products ORDER BY price LIMIT 2;
26. AS - Alias
-> By using the as keyword AS to define an alias for an identifier (such as column name, table name). The alias will be used in displaying the name. It can also be used as reference.
Ex :
-> SELECT productID AS ID , FROM products ORDER BY ID; -- Use alias ID as reference
27. Producing Summary Reports
1. DISTINCT
-> A column may have duplicate values, we could use keyword DISTINCT to select only distinct values.
-> We can also apply DISTINCT to several columns to select distinct combinations of these columns.
Ex -- Without DISTINCT
-> SELECT price FROM products ;
-- With DISTINCT on price
-> SELECT DISTINCT price AS 'Distinct Price' FROM products ;
-- DISTINCT combination of price and name
-> SELECT DISTINCT price, name FROM products;
2. GROUP BY Clause
-> By using Group by clause we can collapse multiple records with a common value into groups .
-> group by cluase is generally use with the aggregrate function .
Ex :
-> SELECT * FROM products GROUP BY productCode;
GROUP BY Aggregate Functions: COUNT, MAX, MIN, AVG, SUM
-> We can apply group by aggregate function to each group to produce group summay report .
3. HAVING clause
-> Having cluase is similar to WHERE , but it can operate on the GROUP BY aggregate functions; whereas WHERE operates only on columns.
4. WITH ROLLUP
-> The WITH ROLLUP clause shows the summary of group summary .
Ex :
-> SELECT productCode, MAX(price), MIN(price), FROM products GROUP BY productCode WITH ROLLUP;
28. Modifying Data - UPDATE
-> Update use for update existing record from the table .
-> for update query the record we use update query with the set Keyword .
-> To modify existing data, use UPDATE ... SET command, with the following syntax:
Query : UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria
Ex : -- Increase the price by 10% for all products
-> UPDATE products SET price = price * 1.1;
29. Deleting Rows - DELETE FROM
-> for removing specific record from the table then we use delete from keyword .
-> Using Delete from keyword for removing Records are NOT recoverable .
Query : DELETE FROM tableName ;
Ex :
-> DELETE FROM products WHERE name LIKE 'Pencil%';
30. Aggregate Function
-> An aggregate function in SQL returns one value after calculating multiple values of a column.
-> We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.
-> Aggregate functions are a vital component of database management systems. They allow us to perform calculations on large data sets quickly and efficiently.
There are 5 types of SQL aggregate functions:
Count() : The COUNT() function returns the number of rows in a database table.
Sum() : The SUM() function returns the total sum of a numeric column.
Avg() : The AVG() function calculates the average of a set of values.
Min() : The MIN() aggregate function returns the lowest value (minimum) in a set of non-NULL values.
Max() : The MAX() aggregate function returns the highest value (maximum) in a set of non-NULL values.
Ex :
1. Select Count(name) from student where city='Mumbai';
2. Select Sum(age) from student ;
3. Select Avg(age) from student ;
4. Select Min(age) from student ;
5. Select Max(age) from student ;
31. Nested Query
-> Nested query us a such query which contains query inside another query .
-> The nested query display the information of student whose age is maximum.
Ex : select * from student where age=(select Max(age) from student);
32. Alter table
-> By using alter table we can change the schema(Layout) of a table.
-> we can perfom add and drop operation using alter table .
1. Add
-> Using alter table we can Add , New column in existing table.
Query : alter table tableName add ColumnName ;
2. Drop
-> By using alter table query we can remove existing column from table.
Query : alter table tableName drop columnName;
33. SQL Constraints
SQL constraints are used to specify rules for the data in a table.
1. PRIMARY KEY
-> A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
-> Per table there should be only one primary key .
-> Primary key is such a field of table which is use to uniqually identify record from the table .
-> Primary key is internally unique as well as Not Null.
2. FOREIGN KEY
-> Prevents actions that would destroy links between tables.
-> A foreign key of a child table is used to reference the parent table.
-> Foreign key constraint can be imposed to ensure so-called referential integrity - values in the child table must be valid values in the parent table.
-> Foreign key is consider as a primary key of one table which is use in another related Table.
-> The concept of foreign key is only useful if our database contains atleast Two Table which are Related in Nature.
3. UNIQUE
-> Ensures that all values in a column are different
-> Unique Constraint is like primary key but in one table there can be multiple unique constaint. due to unique constraint we cant insert the duplicate value.
4. NOT NULL
-> Ensures that a column cannot have a NULL value.
-> The constraint ensure that there should be compulsary value in that perticular column.
5. CHECK
-> Ensures that the values in a column satisfies a specific condition.
-> Check constraint is use to check the value of the specific column.
-> While inserting in new record if we are Not following the check constraint Then new Record is Not going to inserted in the Table.
6. DEFAULT
-> Sets a default value for a column if no value is specified .
-> By using the default constraint we can specify any default value for the column.
34. Composite Key
-> If only one column of a table is Not sufficient to identify the record Uniquely then we can use more than one column to identify Record uniquely.
-> In such a case that more than one column are combinally called Composite Key.
35. Delete Table
-> To the delete the table with all the record we have to use drop table Query .
Query : drop table tableName ;
36. SQL Joins.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
The concept of join use to combine row from Two tables Based on the related column from it .
1. Inner Join.
2. Left Join.
3. Right Join.
1. Inner Join
-> Returns records that have matching values in both tables .
2. Left Join.
-> Returns all records from the left table, and the matched records from the right table .
3. Right Join.
-> Returns all records from the right table, and the matched records from the left table .
37. Union Operator
-> This operator is use to combine the result of two table with select Query ,
Ex :
-> select student rid , student name from student union select * teacher.eid , teacher.tname from teacher.
38. Having Clause
-> We can't use the aggregate function with where clause to avoid that we can use the Having clause with the aggregate functions.