SQL is a language to operate databases; it includes database creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of the SQL language.
Data can be related to any object in consideration.
For example: your name, age, height, weight, etc are some data related to you.
, Image , file , pdf etc can also be considered as data.
A database is a collection of information that is organized so that it can be easily accessed, managed and updated. Computer databases typically contain aggregations of data records or files, containing information about sales transactions or interactions with specific customers.
DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation and management of the database. It ensures that our data is consistent, organized and is easily accessible by serving as an interface between the database and its end users or application softwares.
RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2 and Amazon Redshift are based on RDBMS.
SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
They are generally preferred when it comes to defining or changing the structure of a specific database in the shortest possible times due to security and other concerns. Some of the commands that can be applied and considered directly for this are as follows.
Information that is provided on the slow query log could be huge in size. The query could also be listed over a thousand times. In order to summarize the slow query log in an informative manner, one can use the third-party tool ‘pt-query-digest’.
A user can take an incremental backup in MySQL using Percona XtraBackup.
In such cases when the password is lost, the user should start the DB with skip-grants-table and then change the password. Thereafter, with the new password, the user should restart the DB in a normal mode.
When the data disk is full and overloaded, the way out is to create and soft link and move the .frm and the .idb files into that link location.
BLOBs are binary large object holding huge data. Four types of BLOBs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.
Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are:
The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint. A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).
CREATE TABLE Students ( /* Create table with a single field as primary key */ ID INT NOT NULL Name VARCHAR(255) PRIMARY KEY (ID) ); CREATE TABLE Students ( /* Create table with multiple fields as primary key */ ID INT NOT NULL LastName VARCHAR(255) FirstName VARCHAR(255) NOT NULL, CONSTRAINT PK_Student PRIMARY KEY (ID, FirstName) ); ALTER TABLE Students /* Set a column as primary key */ ADD PRIMARY KEY (ID); ALTER TABLE Students /* Set multiple columns as primary key */ ADD CONSTRAINT PK_Student /*Naming a Primary Key*/ PRIMARY KEY (ID, FirstName);
A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.
CREATE TABLE Students ( /* Create table with a single field as unique */ ID INT NOT NULL UNIQUE Name VARCHAR(255) ); CREATE TABLE Students ( /* Create table with multiple fields as unique */ ID INT NOT NULL LastName VARCHAR(255) FirstName VARCHAR(255) NOT NULL CONSTRAINT PK_Student UNIQUE (ID, FirstName) ); ALTER TABLE Students /* Set a column as unique */ ADD UNIQUE (ID); ALTER TABLE Students /* Set multiple columns as unique */ ADD CONSTRAINT PK_Student /* Naming a unique constraint */ UNIQUE (ID, FirstName);
A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.
Normalization represents the way of organizing structured data in the database efficiently. It includes creation of tables, establishing relationships between them, and defining rules for those relationships. Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database.
Denormalization is the inverse process of normalization, where the normalized schema is converted into a schema which has redundant information. The performance is improved by using redundancy and keeping the redundant data consistent. The reason for performing denormalization is the overheads produced in query processor by an over-normalized structure.
This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.
There are various types of join which can be used to retrieve data and it depends on the relationship between tables.
Inner join return rows when there is at least one match of rows between the tables.
Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table
Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.
Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.
A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.
An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.
There are three types -
This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.
This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.
NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.
A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.
CREATE TABLE Students ( /* Create table with foreign key - Way 1 */ ID INT NOT NULL Name VARCHAR(255) LibraryID INT PRIMARY KEY (ID) FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) ); CREATE TABLE Students ( /* Create table with foreign key - Way 2 */ ID INT NOT NULL PRIMARY KEY Name VARCHAR(255) LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) ); ALTER TABLE Students /* Add a new foreign key */ ADD FOREIGN KEY (LibraryID) REFERENCES Library (LibraryID);
The UNION operator combines and returns the result-set retrieved by two or more SELECT statements. The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.
Certain conditions need to be met before executing either of the above statements in SQL -
SELECT name FROM Students /* Fetch the union of queries */ UNION SELECT name FROM Contacts; SELECT name FROM Students /* Fetch the union of queries with duplicates*/ UNION ALL SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */ MINUS /* that aren't present in contacts */ SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */ INTERSECT /* that are present in contacts as well */ SELECT name FROM Contacts;
A database cursor is a control structure that allows for traversal of records in a database. Cursors, in addition, facilitates processing after traversal, such as retrieval, addition and deletion of database records. They can be viewed as a pointer to one row in a set of rows.
DECLARE @name VARCHAR(50) /* Declare All Required Variables */ DECLARE db_cursor CURSOR FOR /* Declare Cursor Name*/ SELECT name FROM myDB.students WHERE parent_name IN ('Sara', 'Ansh') OPEN db_cursor /* Open cursor and Fetch data into @name */ FETCH next FROM db_cursor INTO @name CLOSE db_cursor /* Close the cursor and deallocate the resources */ DEALLOCATE db_cursor
Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.
Aggregate – max(), count - Calculated with respect to numeric.
Scalar – UCASE(), NOW() – Calculated with respect to strings.
You can simply declare the two dates, and then use the strtotime function to subtract both the dates and find the differences between the days in seconds.
Consider the below example.
date1 =’2018-09-15′;
date2 = ‘2018-10-15’;
days = (strtotime($date1) – strtotime($date2)) / (60 * 60 * 24);
