157A Clauses Flashcards
SQL Create
CREATE DATABSE database_name;
SQL USE
USE database_name;
SQL DROP
DROP DATABASE database_name;
SQL CREATE TABLE
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 dataype,
column2 datatype,
column3 datatype);
SQL DESC
Every table in a database has a structure of its own. To display the structure of database tables, we
use the DESC statements.
DESC table_name;
SQL INSERT INTO
The SQL INSERT INTO Statement is used to insert data into database tables.
INSERT INTO database_name(column1, column2, column3) VALUES (data,data,data);
SQL SELECT
In order to retrieve the result-sets of the stored data from a database table, we use the SELECT
statement.
SELECT * FROM database_name;
or
SELECT column1, column2,column3 FROM table_name;
SQL UPDATE
When the stored data in a database table is outdated and needs to be updated without having to
delete the table, we use the UPDATE statement
UPDATE database_name SET column1 = value1, column2, value2;
SQL DELETE
DELETE FROM table_name WHERE (CONDITION)
SQL DROP TABLE
DROP TABLE table_name;
SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is implemented in SQL to delete the data of the table but not the
table itself.
TRUNCATE TABLE table_name;
SQL ALTER TABLE
The ALTER TABLE statement is used to 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};
SQL DISTINCT CLAUSE
The DISTINCT clause in a database is used to identify the non-duplicate data from a column.
SELECT DISTINCT column1, column2…columnN FROM table_name;
SQL WHERE CLAUSE
the WHERE clause is used to filter rows from a table by applying a condition
SELECT * FROM table_name WHERE age > 20;
SELECT column1, column2….columnN
FROM table_name
WHERE CONDITION;
SQL AND/OR Operators
The AND/OR Operators are used to apply multiple conditions in the WHERE clause.
SELECT column1, column2….columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2
SQL IN Clause
The IN Operator is used to check whether the data is present in the column or not, using the WHERE
clause.
SELECT column1, column2….columnN
FROM table_name
WHERE column_name IN (val-1, val-2,…val-N);
SQL BETWEEN Clause
The BETWEEN Operator is used to retrieve the values from a table that fall in a certain range, using
the WHERE clause
SELECT column1, column2….columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQL LIKE CLAUSE
the like operator is used to retrive the values from the table that match a cretain pattern using the WHERE clause
SELECT column1, column2….columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL ORDER BY clause
use to arrange values in a specific order
SELECT column1, column2, column3 FROM table_name WHERE (CONDITION) ORDER BY column_name{ASC/DESC};
SQL GROUP BY clause
The GROUP BY Clause is used to group the values of a column together
SELECT SUM(column_name) FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQL COUNT function
the COUNT function gives the number of non-null values present in the specified column
SELECT COUNT(column_name) FROM table_name WHERE (CONDITION);
SQL HAVING clause
The HAVING clause is also 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);
SQL CREATE INDEX
to create index on a database table. SQL provides the CREATE INDEX statement.
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,…columnN);
SQL DROP INDEX
used to drop an index from a table
DROP INDEX sample_index on CUSTOMERS;
