SQL Commands – TECHARGE
2.9K
SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.
SQL Commands
Let’s have a look on different sql commands
1. How To Create A New Table
To create a new table in SQL, we use create table command, syntax for the same is given below
You Might Be Interested In
CREATE TABLE TABLENAME(<column name> <datatype> [(<size>)],<column name> <datatype> [(<size>)],. . . );
For Example
CREATE TABLE STUD (NAME VARCHAR2(10),AGE INT,ADDRESS VARCHAR2(15));
*CONSTRAINT:
DEFINITION: A constraint is a condition or check applicable on a field or set of fields.
Two types of constraints are
Column constraints apply only to individual columns.
Table constraints apply to groups of one or more columns
Syntax:
CREATE TABLE TABLENAME(<column name> <datatype> [(<size>)] <column constraints>, <column name> <datatype> [(<size>)] <column constraints>,. . . );
NOT NULL: NOT NULL immediately after the data type (and size) of a column, this means the column can never have empty values(NULL is not empty but stores an empty value).
For Example
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL, AGE INT NOT NULL,ADDRESS VARCHAR2(15));
DIFFERENT CONSTRAINTS:- Sometimes called as Database integrity. A few of them are
UNIQUE CONSTRAINT: It ensures that no two rows have the same value in the specified columns.
For Example:
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL UNIQUE, AGE INT NOT NULL,ADDRESS VARCHAR2(15));
It can be applied only to columns that have also been declared NOT NULL.
PRIMARY KEY:- It declares a column as the primary key of the table. It cannot allow NULL values, thus it must be applied to columns declared as NOT NULL.
For Example:
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL PRIMARY KEY,INT NOT NULL ,ADDRESS VARCHAR2(15));
DEFAULT CONSTRAINT:- A default value can be specified for a column using the DEFAULT clause. When a user does not enter a value for the column(having default value), automatically the defined default value is inserted in the field.
For Example:
CREATE TABLE STUD (STUD_ID INT NOT NULL PRIMARY KEY ,
NAME VARCHAR(2) NOT NULL,
ADDRESS VARCHAR(20) DEFAULT=”NOTKNOWN”);
CHECK CONSTRAINT:- It limits values that can be inserted into a column of a table.
For Example:
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL PRIMARY KEY,
ADDRESS VARCHAR2(15));
*APPLYING TABLE CONSTRAINTS:
When a constraint is to be applied on a group of columns of the table, it is called table constraints. It appear in the end of table definition.
For Example:
CREATE TABLE items(icode char(5) NOT NULL,
descp char(20) NOT NULL,
rol int, qoh integer,
CHECK(rol<qoh),
UNIQUE(icode,descp));
If want to define primary key that contains more than one column u can use PRIMARY KEY constraint.
For Example:
CREATE TABLE members(firstname char(15) NOT NULL,
lastname char(15) NOT NULL,
city char(20) PRIMARY KEY(firstname,lastname));
2. SELECT Command
Select Command is used to retrieve data from a database. The SELECT clause specifies the columns to be displayed in the query result.
Syntax:
SELECT <columnname> [,<columnname>],… FROM <tablename>;
To view all rows and columns.
For Example:
To view specific columns.
For Example:
SELECT NAME, AGE FROM STUD;
To view the columns in any order.
For Example:
SELECT AGE,ADDRESS,NAME FROM STUD;
To eliminate the duplicate data.
For Example: Suppose u want the list of depts. Of your school and avoid repetition of rows then this can be done using DISTINCT keyword in the SELECT clause.
SELECT DISTINCT Dept FROM EXAM;
To see the list of depts. Of your school with duplicate values.
For Example:
SELECT ALL DEPT FROM EXAM;
3. How To view the Table structure
In SQL, you can use the describe command to view the structure of a table, including the field names and data types.
Syntax:
DESC <tablename> ; or DESCRIBE <tablename>;
4. How To select a specific rows(WHERE Clause)
In SQL, the WHERE clause is used to select specific rows from a table.
Syntax:
SELECT <columnname> [,<columnname>],… FROM <tablename> WHERE <condition>;
For Example:
SELECT NAME, AGE FROM STUD WHERE AGE<17;
5. Relational Operators
To compare two values relational operators are used. They are =,>,<,<=,>=,<>
To list all the students not having age 15 .
For Example:
SELECT * FROM STUD WHERE AGE<> 15;
List the students having age 17.
For Example:
SELECT * FROM STUD WHERE AGE=17;
6. Logical Operators (NOT,OR,AND)
These are used to connect search conditions in the WHERE clause.
For Example:
To list the name and department of employees working in production & servicing departments
For Example:
SELECT Ename, Dept FROM EMPLO WHERE Dept=’Production’ OR Dept= ‘Servicing’;
To list the name, salary and city of those employees who are living in Jalandhar and have salary greater than 10,000.
Example:
SELECT Ename, city,salary FROM EMPLO WHERE city=’Jalandhar’ AND salary>10,000;
To list all the employees details whose city are other than ‘Pune’.
Example:
SELECT * FROM EMPLO WHERE (NOT city=’Pune’ );
7. CONDITION BASED ON A RANGE (BETWEEN OPERATOR)
Example:
To list all the employees having salary in the range 3,000 to 5,000
SELECT * FROM EMPLO WHERE SALARY BETWEEN 3000 AND 5000;
To list all the employees having salary not in the range 3,000 to 5,000
SELECT * FROM EMPLO WHERE SALARY NOT BETWEEN 3000 AND 5000;
8. CONDITION BASED ON A LIST (IN OPERATOR)
For Example:
To display a list of members from ‘sPUNE’,’DELHI’,’MUMBAI’.
SELECT * FROM EMPLO WHERE CITY IN (‘PUNE’,’DELHI’,’MUMBAI’);
To display a list of members that do not match the list i.e. ‘PUNE’,’DELHI’,’MUMBAI’.
SELECT * FROM EMPLO WHERE CITY NOT IN (‘PUNE’,’DELHI’,’MUMBAI’);
9. CONDITION BASED ON PATTERN MATCHES patterns are case sensitive
PERCENT (%) matches any substring
To list the employees who are in areas with pincodes starting with 13.
SELECT * FROM EMPLO WHERE pincodes LIKE ‘13%’;
To list the name of employee whose ecode(employee number )ending with 8.
SELECT * FROM EMPLO WHERE ecode LIKE ‘%8’
To list have any substring match like ‘ %JA%’
SELECT * FROM EMPLO WHERE ecode LIKE ‘ %JA%’ ;
UNDERSCORE(_) matches any character
To list the employees having name ending with a
SELECT * FROM EMPLO WHERE name LIKE ‘_ _ _ a’;
‘_ _ _ _’ matches any string of exactly 4 characters.
SELECT * FROM EMPLO WHERE name LIKE ‘_ _ _ _’;
10. SEARCHING FOR NULL
You can perform search using is clause .
For Exapmle
SELECT NAME,AGE FROM STUD WHERE ADDRESS IS NULL;
11. SORTING RESULTS (ORDER BY Clause)
You can sort the result of query in a specific order (ascending or descending) using ORDER BY Clause. Default is ascending .
EX:
SELECT * FROM STUD ORDER BY NAME;
SELECT * FROM STUD ORDER BY NAME DESC;
SELECT * FROM STUD ORDER BY NAME DESC, AGE ASC ;
NOTE: Where ASC is for ascending order.
12. TO PERFORM SIMPLE CALCULATIONS.
As we know that we should have a table name when we are using SELECT command otherwise the
SELECT fails. If we are performing simple calculation like 5 * 3, SQL provide us a dummy table called Dual to perform such calculation which has just one row and one column.
EX: SELECT 4*3 FROM DUAL;
The current date can be obtained from the table dual
EX: SELECT sysdate FROM DUAL;
13. AGGREGATE FUNCTIONS OR GROUPING FUNCTIONS
Aggregate functions are applied to all rows in a table or to a subset of the table specified by a WHERE clause.
Aggregate functions results is a single value. Functions are
avg: to compute average value
min: to find minimum value
max: to find maximum value
sum: to find total value
count: to count non-null values in a column
count(*): to count total no. of rows in a table
For Example:
select sum(age), min(age),max(age),count(age) from stud;
select count(*) from stud;
select count (distinct age) from student;
14. INSERT COMMAND
The INSERT command is a data manipulation language (DML) command that adds new records to a database table.
SYNTAX: INSERT INTO <tablename> VALUES (<Value1>,<Value2>,<Value3>,<Value4>);
To insert values in the table EX:
INSERT INTO stud VALUES(‘Pratap’,17,’Army Area’);
To insert values using & operator
INSERT INTO stud VALUES(‘&name’,age,’&address’);
To insert row with NULL values
INSERT INTO stud(name,age) VALUES (‘Raja’,18);
NOTE: In this fieldnames having datatype char or varchar are not in single quotes.
To insert values from other table.
INSERT INTO table1 SELECT * FROM table2 WHERE condition;
NOTE: In this table1 and table2 are already created and must match the columns o/p by the subquery.
15. UPDATE COMMAND
The UPDATE command in SQL changes the data of one or more records in a table. It is a Data Manipulation Language (DML) command that works on the records of a particular database table.
SYNTAX: UPDATE <tablename> SET <colname>=<value> [,<colname>=<value>,…][ WHERE condition];
For Example:
UPDATE stud SET age=19 WHERE name=’ritu’;
16. DELETE COMMAND
The DELETE command is a data manipulation language (DML) command that removes records from a table. The DELETE command can remove all records from a table at once, or it can delete specific records based on a condition.
SYNTAX: DELETE FROM <tablename> [WHERE condition];
For example:
DELETE FROM stud WHERE AGE=15;
17. DROP TABLE Command
The DROP TABLE command in SQL removes an existing table in a database, along with all its rows, data, indexes, triggers, constraints, and metadata. Once you drop the table ,all the data present in table got also delete as well .
SYNTAX: DROP TABLE <tablename>;
For Example
18. ALTER TABLE command
The ALTER TABLE command in SQL is used to change the structure of an existing table, including adding, modifying, dropping, or renaming columns, adding constraints, and adding a primary key.
To add a column to a table
Syntax: ALTER TABLE <tablename> ADD <columnname> <datatype> <size>;
For Example:
ALTER TABLE stud ADD (marks int );
To delete a column in a table
Syntax : ALTER TABLE <tablename> DROP COLUMN <columnname>;
For Example
ALTER TABLE stud DROP COLUMN marks;
To change the data type of a column in a table
Syntax: ALTER TABLE <table_name> ALTER COLUMN <column_name>< datatype>;
For Example
ALTER TABLE stud
ADD Email varchar(30);
