forked from hotwax/training-assignment
-
Notifications
You must be signed in to change notification settings - Fork 0
SadhanaDeshmukh:Module3SQL
Sadhana Deshmukh edited this page Mar 14, 2023
·
16 revisions
RDBMS stands for Relational Database Management System.
Characteristics of a Relation:
- Each relation has a unique name by which it is identified in the database.
- Relation does not contain duplicate tuples.
- The tuples of a relation have no specific order.
- All attributes in a relation are atomic, i.e., each cell of a relation contains exactly one value.
- A table is the simplest example of data stored in RDBMS.
SQL (Structured Query Language) is used to perform operations on the records stored in the database, such as updating records, inserting records, deleting records, creating and modifying database tables, views, etc.
- Data types are used to represent the nature of the data that can be stored in the database table.
- For example, in a particular column of a table, if we want to store a string type of data
- then we will have to declare a string data type of this column.
Data types mainly classified into three categories for every database.
- String Data types
- Numeric Data types
- Date and time Data types
CHAR(Size)
- It is used to specify a fixed length string that can contain numbers, letters, and special characters.
- Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size)
- It is used to specify a variable length string that can contain numbers, letters, and special characters.
- Its size can be from 0 to 65535 characters.
BINARY(Size)
- It is equal to CHAR() but stores binary byte strings.
- Its size parameter specifies the column length in the bytes. Default is 1.
VARBINARY(Size)
- It is equal to VARCHAR() but stores binary byte strings.
- Its size parameter specifies the maximum column length in bytes.
TEXT(Size)
- It holds a string that can contain a maximum length of 255 characters.
TINYTEXT
- It holds a string with a maximum length of 255 characters.
MEDIUMTEXT
- It holds a string with medium length.
LONGTEXT
- It holds a string with larger length
SET( val1,val2,val3,....)
- It is used to specify a string that can have 0 or more values, chosen from a list of possible values.
- You can list up to 64 values at one time in a SET list.
BIT(Size)
- It is used for a bit-value type.
- The number of bits per value is specified in size.
- Its size can be 1 to 64. The default value is 1.
INT(size)
- It is used for the integer value.
- Its signed range varies from -2147483648 to 2147483647
INTEGER(size)
- It is equal to INT(size).
FLOAT(p)
- It is used to specify a floating point number.
- MySQL used p parameter to determine whether to use FLOAT or DOUBLE.
- If p is between 0 to 24, the data type becomes FLOAT ().
- If p is from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, d)
- It is a normal size floating point number.
- Its size parameter specifies the total number of digits.
- The number of digits after the decimal is specified by d parameter.
DECIMAL(size, d)
- It is used to specify a fixed point number.
- Its size parameter specifies the total number of digits.
- The number of digits after the decimal parameter is specified by d parameter.
- The maximum value for the size is 65, and the default value is 10.
- The maximum value for d is 30, and the default value is 0.
DEC(size, d)
- It is equal to DECIMAL(size, d).
BOOL
- It is used to specify Boolean values true and false.
- Zero is considered as false, and nonzero values are considered as true.
DATE
- It is used to specify date format YYYY-MM-DD.
- Its supported range is from '1000-01-01' to '9999-12-31'.
DATETIME(fsp)
- It is used to specify date and time combination.
- Its format is YYYY-MM-DD hh:mm:ss.
- Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'.
TIMESTAMP(fsp)
- It is used to specify the timestamp.
- Its value is stored as the number of seconds since the Unix epoch('1970-01-01 00:00:00' UTC).
- Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
TIME(fsp)
- It is used to specify the time format.
- Its format is hh:mm:ss. Its supported range is from '-838:59:59' to '838:59:59'
YEAR
- It is used to specify a year in four-digit format.
- Values allowed in four digit format from 1901 to 2155, and 0000.
- The precedence of SQL operators is the sequence in which the
- SQL evaluates the different operators in the same expression.
- Structured Query Language evaluates those operators first, which have high precedence
- The operators at the top have high precedence, and
- The operators that appear at the bottom have low precedence.
- "**" => Exponentiation operator
- "+, -" =>Identity operator, Negation operator
- "*, /" => Multiplication operator, Division operator
- "+, -, ||"=>Addition (plus) operator, subtraction (minus) operator, String Concatenation operator
- "=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN" =>Comparison Operators
- "NOT" => Logical negation operator
- "&& or AND" => Conjunction operator
- "OR" => Inclusion operator
For storing data in tables, first we need to create database
CREATE DATABASE Database_Name;
To check names of database in our device:
SHOW DATABASE;
If you want to replace the existing oldDB database, then you have to type the following SQL query:
CREATE OR REPLACE DATABASE oldDB ;
To create a table in choose database
CREATE TABLE STUDENTS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
PRIMARY KEY (ID)
);
It will delete entire table
DROP TABLE "table_name";
It is used to delete a particular row from table
DELETE FROM table_name [WHERE condition];
It is used to delete all the rows from the table.
TRUNCATE TABLE tableName;
The RENAME and ALTER helps us to rename the table_names
RENAME statement
RENAME old_table _name To new_table_name ;
OR
ALTER TABLE old_table_name RENAME TO new_table_name;
- SQL INSERT statement is a SQL query.
- It is used to insert a single or a multiple records in a table.
- There are two ways to insert data in a table:
INSERT INTO table_name
VALUES (value1, value2, value3....);
OR
INSERT INTO table_name (column1, column2, column3....)
VALUES (value1, value2, value3.....);
- SQL UPDATE statement is used to change the data of the records held by tables.
- Which rows is to be update, it is decided by a condition.
- To specify condition, we use WHERE clause.
UPDATE table_name SET [column_name1= value1,... column_nameN = valueN] [WHERE condition]
- The SQL DELETE statement is used to delete rows from a table.
- Generally DELETE statement removes one or more records from a table.
DELETE FROM table_name [WHERE condition];
- The ALTER TABLE statement in SQL allows you to add, modify, and delete columns of an existing table.
- This statement also allows database users to add and remove various SQL constraints on the existing tables.
- Any user can also change the name of the table using this statement.
ALTER TABLE ADD Column
ALTER TABLE table_name ADD column_name column-definition;
ALTER TABLE TO CHANGE DEFINATION OF COLUMN
ALTER TABLE table_name MODIFY existing_column_name new_column_definition;
ALTER TABLE TO DROP PARTICULAR COLUMN
ALTER TABLE table_name DROP Column column_name;
- SELECT INTO statement in Structured Query Language copies the content from one existing table into the new table.
- SQL creates the new table by using the structure of the existing table.
SELECT * INTO New_table_name FROM old_table_name;
OR
SELECT * INTO New_table_name FROM old_table_name WHERE [ condition ] ;
- Temporary tables can be created at run-time
- And can do all kinds of operations that a normal table can do.
- These temporary tables are created inside tempdb database. There are two types of temp tables based on the behavior and scope -
- Local Temp Variable
- Global Temp Variable
Local Temp Variable
- Local temp tables are only available at current connection time.
- It is automatically deleted when user disconnects from instances.
- It is started with hash (#) sign.
CREATE TABLE #local temp table (
User id int,
Username varchar (50),
User address varchar (150)
)
Global Temp Variable
- Global temp tables name starts with double hash (##).
- Once this table is created, it is like a permanent table.
- It is always ready for all users and not deleted until the total connection is withdrawn.
CREATE TABLE ##new global temp table (
User id int,
User name varchar (50),
User address varchar (150)
)
- The DELETE statement only deletes the rows from the table based on the condition defined by
- WHERE clause or delete all the rows from the table when condition is not specified.
- But it does not free the space containing by the table.
- The TRUNCATE statement is used to delete all the rows from the table and free the containing space.
- On comparing TRUNCATE & DROP, drop delete all existence of table, while truncate clears the data only.
SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Name;
OR
SELECT * FROM table_name;
SELECT UNIQUE is an old syntax which was used in oracle description but later ANSI standard defines DISTINCT as the official keyword.
SELECT UNIQUE column_name
FROM table_name;
The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique data.
SELECT DISTINCT column_name ,column_name
FROM table_name;
- The SELECT TOP statement in SQL shows the limited number of records or rows from the database table.
- The TOP clause in the statement specifies how many rows are returned.
SELECT TOP 3 Column_Name_1, Column_Name_2 FROM table_name;
- WHERE clauses are not mandatory clauses.
- But it can be used to limit the number of rows affected by a select statement or returned by a query.
- Actually, it filters the records. It returns only those queries which fulfill the specific conditions.
- WHERE clause is used in SELECT, UPDATE, DELETE statement etc.
SELECT column1, column 2, ... column n
FROM table_name
WHERE [conditions]
AND
- The SQL AND condition is used in SQL query to create two or more conditions to be met.
- It is used in SQL SELECT, INSERT, UPDATE and DELETE
SELECT * FROM employee WHERE Department = "IT" AND Location = "Chennai";
OR
- The SQL OR condition is used in SQL query to create a SQL statement where records are returned when any one condition met.
- It can be used in a SELECT statement, INSERT statement, UPDATE statement or DELETE statement.
SELECT columns FROM tables WHERE condition 1 OR condition 2;
AS
- The As is used to give alice name to the column or table
SELECT Column_Name1 AS New_Column_Name FROM Table_Name;
- Group By statement is used for organizing similar data into groups.
- The data is further organized with the help of equivalent function.
- It means, if different rows in a precise column have the same values, it will arrange those rows in a group.
- The SELECT statement is used with the GROUP BY clause in the SQL query.
- WHERE clause is placed before the GROUP BY clause in SQL.
- ORDER BY clause is placed after the GROUP BY clause in SQL.
SELECT column1, aggregate function(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;
- The HAVING clause places the condition in the groups defined by the GROUP BY clause in the SELECT statement.
- This SQL clause is implemented after the 'GROUP BY' clause in the 'SELECT' statement.
SELECT column_Name1, aggregate_function_name(column_Name)
FROM table_name
GROUP BY column_Name1
HAVING condition;
Order by clause is used to order table in an order according to specific column name in ascending or descending order If we are not writing anything with order by then it is considered to be asc.
SELECT * FROM table_name ORDER BY column_name;
OR
SELECT * FROM table_name ORDER BY column_name asc;
- The join takes records from two or more tables in a database and combines it together
- If you want to access more than one table through a select statement.
- If you want to combine two or more table then join statement is used .
- It combines rows of that tables in one table and one can retrieve the information by a SELECT statement. Types of joins -
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied.
- This keyword will create the result-set by combining all rows from both the tables where the condition satisfies
- i.e value of the common field will be the same.
SELECT table1.column1,table1.column2
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
- The left join returns all the values from the left table and
- It also includes matching values from right table
- If there are no matching join value it returns NULL.
SELECT table1.column1, table2.column2....
FROM table1
LEFT JOIN table2
ON table1.column_field = table2.column_field;
- The right join returns all the values from the rows of right table.
- It also includes the matched values from left table
- But if there is no matching in both tables, it returns NULL.
SELECT table1.column1, table2.column2.....
FROM table1
RIGHT JOIN table2
ON table1.column_field = table2.column_field;
- The full join is the result of combination of both left and right outer join
- And the join tables have all the records from both tables.
- It puts NULL on the place of matches not found.
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
A column or columns is called primary key (PK) that uniquely identifies each row in the table. If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table. When multiple columns are used as a primary key, it is known as composite primary key.
CREATE TABLE students
(
S_Id int NOT NULL PRIMARY KEY,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
)
OR
ALTER TABLE students
ADD PRIMARY KEY (S_Id)
In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables. In simple words you can say that, a foreign key in one table used to point primary key in another table.
CREATE TABLE orders
(
O_Id int NOT NULL,
Order_No int NOT NULL,
S_Id int,
PRIMAY KEY (O_Id),
FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)
)
OR
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY(S_Id)
REFERENCES Students (S_Id)
- 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
- when the columns are combined uniqueness is guaranteed
- but when it taken individually it does not guarantee uniqueness.
- a primary key that is made by the combination of more than one attribute is known as a composite key.
- A view is a database object that has no values.
- Its contents are based on the base table.
- It contains rows and columns similar to the real table.
- In MySQL, the View is a virtual table created by a query by joining one or more tables.
- It is operated similarly to the base table but does not contain any data of its own.
- The View and table have one main difference that the views are definitions built on top of other tables (or views).
- If any changes occur in the underlying table, the same changes reflected in the View also.
Create view
CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
All other commands are same for insertion, deletion, updation but we have to write view instead of column
- A lock is a mechanism associated with a table used to restrict the unauthorized access of the data in a table.
- MySQL allows a client session to acquire a table lock explicitly to cooperate with other sessions to access the table's data.
- MySQL also allows table locking to prevent it from unauthorized modification into the same table during a specific period.
- A session in MySQL can acquire or release locks on the table only for itself.
- Therefore, one session cannot acquire or release table locks for other sessions.
- It is to note that we must have a TABLE LOCK and SELECT privileges for table locking.
- Table Locking in MySQL is mainly used to solve concurrency problems.
- It will be used while running a transaction,
- i.e., first read a value from a table (database) and then write it into the table (database).
This lock allows a user to only read the data from a table.
This lock allows a user to do both reading and writing into a table.
We have specified the table name on which we want to acquire a lock after the LOCK TABLES keywords. We can specify the lock type, either READ or WRITE.
LOCK TABLES table_name [READ | WRITE];
We can also lock more than one table in SQL by using a list of comma-separated table's names with lock types. See the below syntax:
LOCK TABLES tab_name1 [READ | WRITE], tab_name2 [READ | WRITE],...... ;
To perform better concurrency along with isolation we use different modes of locks-
Shared mode: Denoted by lock-S(Q) transaction can perform read operation, any other transaction can also obtain same lock on same data item at same time.
Exclusive mode: Denoted by lock-X(Q) , transaction can perform both read/write operations any other transaction can not obtain either shared/exclusive mode lock
Here we have some set of rules that can help us to use locking system efficiently and help us to work concurrently
- This locking system says, a transaction cannot unlock a resource until is done on applying all its required locks
- It says there are two phases growing phase and shrinking phase and there is something called as lock point as well
- growing phase is something when transactions are applying locks on there required resources
- lock point is something when transaction has acquired all required locks
- and the phase where each transaction is unlocking resources is called as shrinking phase
This can cause deadlock hence we have a updated version of this which says -
- We should have knowledge of all required resources before starting the transaction
- And we apply locks on resources at the beginning only so that our resources cannot be own by someone else and deadlock doesn't happens
- Here we starts from lock point we don't have growing phase.
- Possibility of irrecoverable schedules
- Deadlock is not considered to be a big or huge problem in real world while irrecoverable schedules are to solve that problem we have this
- Ir-recoverability happens because of dirty read problem which is on transaction is reading uncommitted data from another transaction
- So this locking system says do not unlock any resources if we don't unlock anything then it will not be able to read form another transactions
- So there is only growing phase and lock point when we are done with our work we should directly commit it
- Ones we commit every lock hold by that transactions are released automatically.
- This can reduce concurrency, it is inefficient and deadlock can occur
- It says why dirty read problem is happening because we are reading uncommitted changes of some other transactions.
- What we can do it is we should unlock shared lock but not the exclusive lock because if we are reading from a shared lock and its uncommitted dirty read will not be generated.
- So dirty read is happening just because of exclusive locks which are uncommitted.
- So we should unlock shared locks and directly commit if we use exclusive lock
- There are 2 phases growing and partial shrinking phase where we will be unlocking only shared locks.
- And there is a lock point also, here chances of deadlock occurrence are possible but resolves the recoverability issue.
- The IF function is one of the parts of the MySQL control flow function, which returns a value based on the given conditions.
- In other words, the IF function is used for validating a function in MySQL.
- The IF function returns a value YES when the given condition evaluates to true
- And returns a NO value when the condition evaluates to false.
- It returns values either in a string or numeric form depending upon the context in which this function is used.
- Sometimes, this function is known as IF-ELSE and IF THAN ELSE function.
- Syntax : IF ( expression 1, expression 2, expression 3)
SELECT IF(200>350,'YES','NO');
SELECT IF(251 = 251,' Correct','Wrong');
- The IFNULL function accepts two expressions & if the first expression is not null, it returns the first arguments.
- If the first expression is null, it returns the second argument.
- This function returns either string or numeric value, depending on the context where it is used.
- Syntax : IFNULL (Expression1, Expression2)
SELECT IFNULL("Hello", "There");
- The NULLIF function accepts two expressions,
- and if the first expression is equal to the second expression,
- it returns the NULL. Otherwise, it returns the first expression.
- Syntax : NULLIF (Expression1, Expression2)
SELECT NULLIF("Tea", "point");
- The CASE expression validates various conditions and returns the result when the first condition is true.
- Once the condition is met, it stops traversing and gives the output.
- If it will not find any condition true, it executes the else block.
- When the else block is not found, it returns a NULL value.
- The main goal of MySQL CASE statement is to deal with multiple IF statements in the SELECT clause.
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
- Normalization is the process of organizing data in a relational database to reduce redundancy and dependency.
- It is a technique used to eliminate data anomalies and improve database efficiency.
- Normalization helps to ensure data consistency and integrity in a database, as well as improving database performance and reducing storage requirements.
- However, excessive normalization can lead to increased complexity and decreased performance, so it is important to
- find the right balance between normalization and denormalization for each specific use case.
- There are several levels of normalization, with each level having a set of rules that define the requirements for a table to be considered normalized.
The most commonly used levels of normalization are:
- A table is in 1NF if it has a primary key and every column contains atomic (indivisible) values.
- This eliminates repeating groups of data in a single row and ensures that every row has a unique identifier.
- A table is in 2NF if it is in 1NF and every non-key column is fully dependent on the primary key.
- This eliminates partial dependencies in a table, where a non-key column depends on only a part of the primary key.
- A table is in 3NF if it is in 2NF and every non-key column is independent of each other.
- This eliminates transitive dependencies in a table, where a non-key column depends on another non-key column, rather than on the primary key.
- A table is in BCNF if it is in 3NF and for every functional dependency A -> B, A is a super key.
- This eliminates certain types of data anomalies that can occur in 3NF tables where a non-key attribute determines another non-key attribute.