The Insert Into Select statement

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. This statement requires that the data types in source and target tables matches. The existing records in the target table are unaffected. SYNTAX ISNERT INTO table 2 SELECT * FROM table 1 WHERE condition; One can also copy only...

EXISTS, ANY, ALL

EXISTS: The EXISTS operator is used to check the existence of record in the subquery. It returns TRUE if the subquery returns one or more records. SYNTAX: SELECT column name(s) FROM table name WHERE EXISTS (SELECT column name FROM table name WHERE condition); ANY & ALL : These operators allow you to perform a comparison...

GROUP BY & HAVING

GROUP BY The GROUP BY statement groups rows that have the same values into summary rows. This statement is often used with aggregate functions like COUNT(), MAX() , MIN() , SUM() , AVG() to group the result-set by one or more columns. Syntax: SELECT <column name(s)> FROM table name WHERE condition GROUP BY <column name(s)>...

UNION

The MySQL UNION operator is used to combine the result-set of two or more SELECT statements. To apply the UNION operator one must remember following things: Every SELECT statement within UNION must have the same number of columnsThe columns must also have similar data typesThe columns in every SELECT statement must also be in the same order Syntax: SELECT <column name(s)> FROM table1...

JOINS

The JOIN clause is used to combine rows from two or more tables, based on a related column between them. JOINS are of five types. INNER JOINLEFT JOINRIGHT JOINCROSS JOINSELF JOIN INNER JOIN The INNER JOIN keyword selects records that have matching values in both tables. INNER JOIN Syntax: SELECT <column name(s)> FROM table1 INNER...

Column Aliases

The column that you select in a query can be given a different name i.e. column alias name for output purposes. For instance consider a table named school and it has a column class, so the name of the column class can be changed to any other name like Standard and can be displayed as...

SUM, AVG & COUNT

SUM SUM() is used to calculate the total numeric sum of a column, i.e. all the numeric values will be added and will be displayed as result. SYNTAX select SUM(columnname) from tablename where condition; EXAMPLE select SUM(Score) from students; AVG AVG() returns the average of the numeric values of a specific column. SYNTAX select AVG(columnname)...

MAX, MIN & LIMIT

MAX() is used to find out the maximum value of a selected column. Where as MN() is used to find the minimum value. The use of both max and min is very simple in MYSQL, even a child can learn to use it! SYNTAX: MAX() : Select MAX(columnname) From tablename Where condition; MIN(): Select MIN(columnname)...

DELETE , ALTER & DROP Command

DELETE While working with tables, one may reach a situation where he/she no longer needs some rows of data. In such a case one would like to remove such rows. This can be done by using the DELETE command. The DELETE command removes rows from a table. This removes the entire rows, not individual field...

UPDATE Table

UPDATE Sometimes one needs to change some or all the values in a particular row that is already existing. This can be done using the UPDATE command. It specifies the rows to be changed using the WHERE clause, and the new data using the SET keyword. The new data can be a specified constant, an...

Handling NULLS

The empty values are represented as NULLS in a table. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. A NULL value is different from ZERO. NULL...

ORDER BY Clause

Whenever a SELECT query is executed, the resulting rows emerge in a pre-decided order. One can sort the results or a query in a specific order using the 'ORDER BY' clause. The ORDER BY clause allows string of query results by one or more columns. The sorting can be done either in ascending order or...

AND , OR & NOT

AND, OR & NOT are used when the conditions for selecting an entry are more specific. These keywords are used along with WHERE. They are the logical operators. They can also be used with these symbols. AND (&&) OR (||) NOT (!) Syntax: 1. SELECT column name...[column names] from Table name WHERE(condition 1 AND condition...

The WHERE Clause

Sometimes a table may contain a huge number of rows, so when the user wants to see a particular row or a particular data and selects all the rows , then it will be problematic as it takes longer time to find out that particular information from such a big table, So in order to...

Select Query

The select query is used to display the contents of the table. It can either be used to display the entire table, specific columns, columns with some particular values or specific cases. The different ways in which select can be used are as follows: 1 . Select * It is used to select all the...

Show Databases, Show Tables and Desc Table Queries

Show databases: It is used to display the list of all the databases in your MySQL. It is used in the following manner. show databases; Show Tables: It is display the of all the tables in the current database. Here's how to use it. show tables; The output above displays the list of the tables...

Inserting values in a table in MySQL

After the creation of table one needs to insert the values. This is done using the 'INSERT INTO' query, it is used in many ways. The examples below show the uses on this query. Example 1 insert into students values(1,'Emily',12,'A','Physics'); insert into students values(2,'Lilly',12,'C','Biology'); In the above query after the 'insert into' command we write...

Creating Tables in MySQL

After creating the database, the user must know the queries to create table, insert values in the table, selecting the values and displaying them, deleting a row, etc. CREATE TABLE To create table the 'CREATE TABLE' query is used as shown in the example below. create table students(Roll int, Name varchar(10), Class int, Section varchar(5),...

Databases in MySQL

To start with databases in MySQL we use different queries for creating and using the databases. First of all to start with the basics we must know how to create a database in MySQL. It is done using the 'CREATE DATABASE (name)' query. Simply open the MySQL Command Line Client on your PC/laptop. Enter your...

Classification of SQL Statements

SQL, technically speaking is a data sublanguage that is it is a language used to interact with database. In other words all SQL statements are instructions to the database only. And that is where it differs from general purpose programming languages like C or CPP. SQL provides many different types of commands used for different...

Data Definition Language (DDL)

A database scheme is specified by a set of definitions which are expressed by a special language called a data definition language DDL the result of compilation of DDL statements is a set of tables which are stored in a special file called data dictionary or directory. Whenever a data is read or modified in...

Processing Capabilities of SQL

The SQL has proved to be a language that can be used by both casual users as well as skilled programmers. It offers a variety of processing capabilities, simpler ones of which maybe used by the former and the more complex by the latter class of users. Processing capabilities are as follows: Data definition language...

Keys

It is important to be able to specify how rows in relation are distinguished conceptually, rows are distinct from one another, but from a database perspective the difference among them must be expressed in terms of their attributes. Keys come here for a rescue! Primary key A primary key is a special relational database table...

Components of a table and Terminologies in RDBMS

Byte : A byte is a group of eight bits and is used to store a character.Data Item : A data item is the smallest unit of named data. It may consist of any number of bits and bytes.Field: A data item represents on type of information and often referred to as field or data...

Relational Database Model

In relational database model, the data is organized into tables(i.e. , rows and columns). These table are called relations. A row in a table represents a relationship among a set of values. Since a table is a collection of such relationships, it is generally referred to using the mathematical term, relation, from which the relational...

DBMS

Introduction A database can be defined as a collection of interrelated data stored together to serve multiple actions. It is basically a computer based record keeping system. The collection of data usually referred to as the database, contains information about one particular enterprise. The data is stored in a such a way that it is...

MySQL

Introduction to MySQL MySQL is a freely available open source Relational Database Management System (RDBMS) which uses Structured Query Language (SQL). User can store the information in the form of tables in a MySQL databases. One database can have as many tables as the user wants to store, and each table can have as many...