SQL Syntax

Question 1

CREATE DATABASE

Answer 1

To store data within a database, you first need to create it. This is necessary to individualize the data belonging to an organization.

CREATE DATABASE sampleDB

Question 2

USE

Answer 2

Once the database is created, it needs to be used in order to start storing the data accordingly.

USE sampleDB;

Question 3

DROP DATABASE

Answer 3

If a database is no longer necessary, you can also delete it.

DROP DATABASE sampleDB;

Question 4

CREATE TABLE

Answer 4

In an SQL driven database, the data is stored in a structured manner, i.e. in the form of tables.

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
…..
columnN datatype,
PRIMARY KEY( one or more columns )
);

CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

Question 5

INSERT INTO

Answer 5

used to insert data into database tables.

INSERT INTO table_name( column1, column2….columnN)
VALUES ( value1, value2….valueN);

INSERT INTO CUSTOMERS VALUES
(1, ‘Ramesh’, 32, ‘Ahmedabad’, 2000.00 ),
(2, ‘Khilan’, 25, ‘Delhi’, 1500),
(3, ‘kaushik’, 23, ‘Kota’, 2000),
(4, ‘Chaitali’, 25, ‘Mumbai’, 6500),
(5, ‘Hardik’, 27, ‘Bhopal’, 8500),
(6, ‘Komal’, 22, ‘Hyderabad’, 4500),
(7, ‘Muffy’, 24, ‘Indore’, 10000);

Question 6

SELECT

Answer 6

retrieve the result-sets of the stored data from a database table.

SELECT column1, column2….columnN FROM table_name;

Question 7

UPDATE

Answer 7

When the stored data in a database table is outdated and needs to be updated without having to delete the table

UPDATE table_name
SET column1 = value1, column2 = value2….columnN=valueN
[ WHERE CONDITION ];

UPDATE CUSTOMERS SET ADDRESS = ‘Pune’ WHERE ID = 6;

Question 8

DELETE

Answer 8

Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions.

DELETE FROM table_name WHERE {CONDITION};

DELETE FROM CUSTOMERS WHERE ID = 6;

Question 9

DROP TABLE

Answer 9

To delete a table entirely from a database when it is no longer needed

DROP TABLE table_name;

Question 10

TRUNCATE TABLE

Answer 10

delete the data of the table but not the table itself.

TRUNCATE TABLE table_name;

Question 11

ALTER TABLE

Answer 11

alter the structure of a table. For instance, you can add, drop, and modify the data of a column using this statement.

ALTER TABLE table_name
{ADD|DROP|MODIFY} column_name {data_type};

ALTER TABLE CUSTOMERS ADD SEX char(1);

used to change the name of a table as well

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE CUSTOMERS RENAME TO NEW_CUSTOMERS;

Question 12

DISTINCT

Answer 12

used to identify the non-duplicate data from a column.

SELECT DISTINCT column1, column2….columnN FROM table_name;

Question 13

WHERE

Answer 13

used to filter rows from a table by applying a condition

SELECT column1, column2….columnN
FROM table_name
WHERE CONDITION;

Question 14

AND/OR

Answer 14

used to apply multiple conditions in the WHERE clause

SELECT column1, column2….columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;

Question 15

IN

Answer 15

used to check whether the data is present in the column or not

SELECT column1, column2….columnN
FROM table_name
WHERE column_name IN (val-1, val-2,…val-N);

Question 16

BETWEEN

Answer 16

used to retrieve the values from a table that fall in a certain range

SELECT column1, column2….columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

Question 17

LIKE

Answer 17

used to retrieve the values from a table that match a certain pattern

SELECT column1, column2….columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

Question 18

ORDER BY

Answer 18

used to arrange the column values in a given/specified order

SELECT column1, column2….columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

Question 19

GROUP BY

Answer 19

used to group the values of a column together

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

Question 20

COUNT

Answer 20

gives the number of non-null values present in the specified column

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

Question 21

HAVING

Answer 21

used to filter a group of rows by applying a condition

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

Question 22

CREATE INDEX

Answer 22

To create an index on a database table
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,…columnN);

Question 23

DROP INDEX

Answer 23

used to drop an index from a table.

DROP INDEX index_name ON table_name;