SELECT and SELECT DISTINCT SQL QUERY

SELECT STATEMENT

SELECT identifies what columns to fetch and FROM identifies which table to refer. The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data query language (DQL) command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

The SELECT statement has many optional clauses:

WHERE specifies which rows to retrieve.

GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.

HAVING selects among the groups defined by the GROUP BY clause.

ORDER BY specifies an order in which to return the rows.

AS keyword provides an alias which can be used to temporarily rename tables or columns.

Syntax

SELECT *| {[DISTINCT] COLUMN_NAME|EXPRESSION [ALIAS],...}

FROM TABLE_NAME;

SELECT	Is a list of one or more columns
*	Selects all columns
DISTINCT	Suppresses duplicates
column|expression	Selects the named column or the expression
alias	Gives selected columns different headings
FROM table	specifies the table to refer containing the columns

Example

Below SELECT example will show all the rows from students table.

SELECT * FROM STUDENTS;

DISTINCT STATEMENT

A column may contain several duplicate values; and generally you merely wish to list the various (distinct) values. The SELECT DISTINCT clause is used with SELECT statement to eliminate all the duplicate records and show only distinctive records. In SQL, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value. DISTINCT can be used with aggregates function and operates only on a single column.

Syntax

SELECT DISTINCT COLUMN_NAME1, COLUMN_NAME2, ...
FROM TABLE_NAME;

Example

DISTINCT clause is used to find the unique values within one column in a table.

Below example shows 6 city from students table. There is a duplicate entry of central city.

SELECT CITY FROM STUDENTS;

By using DISTINCT clause we can eliminate this duplicity and fetch only unique city names.

SELECT DISTINCT CITY FROM STUDENTS;

ALTER TABLE SQL QUERY

The ALTER TABLE Statement

Once the table is created, and sometimes we need to change the table structure or column properties need to be changed, or we need to remove certain columns. We need to use ALTER TABLE statement. Use the ALTER TABLE statement to perform operations like adding a new column, modify an existing column, defining a default value for the new column and drop a column.

Syntax

ALTER TABLE table_name

ADD (column datatype [DEFAULT expr], column datatype...);

ALTER TABLE table_name

MODIFY (column_name datatype [DEFAULT expr], column datatype...);

ALTER TABLE table_name

DROP (column column_name);

table_name	It is the table name
ADD|MODIFY|DROP	Modification clause
Column_name	New column name which is to be added. Existing column name which is to be modified or dropped.
datatype	I s the data type and length of the new column
DEFAULT expr	The default value for a new column

ADD
ALTER TABLE table_name

ADD (column datatype [DEFAULT expr], column datatype...);

Example
Let us now add Country column to students table.
alter table students

add Country varchar(05);

select * from students;

The new column becomes the last column. The new column is initially NULL for all the rows. We will try updating table for this column.

update students set country='NEWYORK'

where CITY='Central';

Above error occurred since the width defined was 05 and we tried to update with value of 7. This is one of the scenario in which we require modification of column data type length which is done with use of MODIFY clause as shown below syntax.

MODIFY

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr], column datatype...);

We can increase the precision of a numeric column using this clause. Also can increase the size of numeric or character columns. We can decrease the width of a column only if the column contains only null values or if the table is empty i.e. has zero rows. We are restricted to change the data type only if the column contains null values. Also perform data type conversion such as converting a CHAR to the VARCHAR2 data type or a VARCHAR2 to the CHAR only if the column contains null values or if you do not change the size.

Example

Modifying VARCHAR value from 05 to 50 for column Country in students table.

alter table students

modify Country varchar(50);

update students set country='NEWYORK'

where CITY='Central';

commit;

select * from students;

DROP

ALTER TABLE table

DROP (column);

When we don’t need some columns in our table we can use  the DROP COLUMN clause to drop columns. It is not mandatory to have data in to drop a column. The table must have at least one column remaining in it after it is altered.  Only one column can be dropped at a time. Once a column is dropped, it cannot be recovered.

Example

Removing the Country column from students table.

alter table students

drop column Country;

select * from students;

SET UNUSED

The SET UNUSED option is use to mark one or more columns as unused.

DROP UNUSED COLUMNS option is used to remove the columns that are marked as unused.

ALTER TABLE table

SET UNUSED (column_name);

OR

ALTER TABLE table

SET UNUSED COLUMN column_name;

ALTER TABLE table

DROP UNUSED COLUMNS;

When a column is dropped from a table, it will also drop any other columns in that table that are marked with the SET UNUSED option. The SET UNUSED option marks one or more columns as unused so that they can be dropped on demand when a system resource is lower. Setting a column as UNUSED just marks a column for logical deletion and the columns are no longer available for DML, DDL or SELECT commands. But the data in the columns is still intact in the segment and not dropped. UNUSED is much faster and as it doesn't update the data file with the changes. The actual DROP will take place after issuing DROP UNUSED columns.

Therefore, if the intent  is to cut-off access to columns without I/O latency in production during peak business hours, then first mark it as UNUSED and then during the weekend or off peak hours, complete the physical drop of the column using DROP UNUSED.

DELETE a row from table SQL query

The SQL DELETE STATEMENT

The DELETE statement is used to delete existing records in a table. The DELETE statement does not return any rows; that is, it will not generate a result set. Executing a DELETE statement can cause triggers to run that can cause deletes in other tables. For example, if two tables are linked by a foreign key and rows in the referenced table are deleted, then it is common that rows in the referencing table would also have to be deleted to maintain referential integrity. We can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Example

Let us see all the records of STUDENTS_COPY table first.

SELECT * FROM STUDENTS_COPY;

The following SQL statement deletes the student   "Oliver Queen" from the STUDENTS_COPY  table.

DELETE FROM STUDENTS_COPY

WHERE ROLLNO=4;

COMMIT;

We can see below that the record for the Oliver Queen is not present in the table.

SELECT * FROM STUDENTS_COPY;

Example

We can delete all rows in a table without deleting the table, which means that the table structure, attributes, and indexes will be not altered.

DELETE FROM STUDENTS_COPY;

COMMIT;

SELECT * FROM STUDENTS_COPY;

Example using EXIST clause

When we want to delete records in one table based on values in another table. Since we cannot list more than one table in the FROM clause while performing a delete, there we can use the EXIST clause.

Now we will delete record from STUDENTS_COPY table based on condition met on MARKS table.

SELECT * FROM MARKS;

SELECT * FROM STUDENTS_COPY;

DELETE FROM STUDENTS_COPY

WHERE EXISTS

  (SELECT *

   FROM MARKS

   WHERE MARKS.ROLLNO = STUDENTS_COPY.ROLLNO

   AND MARKS.ROLLNO =12);

SELECT * FROM STUDENTS_COPY;

We can see that the record for "Leonard Snart" is removed from the STUDENTS_COPY table based on ROLLNO.  mentioned in where clause on MARKS table.

SQL query to UPDATE table in the DATABASE

The UPDATE Statement

When we need to modify existing row, we need to use the UPDATE statement. If needed, we can update multiple records at the same time. For the UPDATE to be successful the user must have data manipulation privileges (UPDATE privilege) on the table or column and the updated value must not conflict with all the applicable constraints (such as primary keys, unique indexes, CHECK constraints, and NOT NULL constraints).

Syntax

UPDATE table_name

SET column_name = value, column_name = value...

WHERE condition;

table_name	It is the table name
SET	Modification clause
column_name	Existing column name which is to be modified.
value	New value to be updated to the column or subquery for the column.
condition	Basis on which the modification is performed. It can include column names, expressions, constants, subqueries, and comparison operators

Example

Let us see the output row from students table where the rollno is 4.

select * from students

where rollno=4;

We will update the lastname for the student whose roll no is 4 with use of update statement example as shown below.

update students

set lastname='Green'

where rollno=4;

commit;

select * from students

where rollno=4;

We can see that the Oliver’s lastname has been updated from Queen to Green.

We must prefer the primary key to identify a single row. Using other columns may unexpectedly cause several rows to be updated. If we omit the WHERE clause, ALL records will be updated.

update students

set lastname='Green';

commit;

The above query will update all the rows in students table and set lastname to Green.

select * from students;

So the UPDATE statement should be used with caution.

Updating Multiple Columns with a Subquery

It is possible to update multiple columns in the single query. Also there is provision to take the value which has to be set as an output from the sub query. A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. The SELECT query of a subquery is always enclosed in parentheses. 

Syntax

UPDATE table_name

SET column_name = (SELECT column_name FROM table_name WHERE condition),

      column_name = (SELECT column_name FROM table_name WHERE condition)

WHERE condition;

Example

Let us update students table using subquery on the same students table as shown below. Here we are updating firstname and city column for row with roll no 45.

update students

set lastname=(select firstname from students where rollno=14),

      CITY=(select CITY from students where rollno=21)

where rollno=45;

commit;

The lastname column in the outer query is populated by subquery which refers to firstname column of students where roll no is 14.

The city column the outer query is populated by subquery which refers to city column of students where roll no is 21.

Before update the output of students table is as shown below.

select * from students;

After update the output of students table is as shown below.

select * from students;

Updating Rows Based on another Table

We can update multiple columns as well as from multiple sub queries and also refer to multiple tables.

Syntax

UPDATE table_name

SET column_name = (SELECT column_name FROM table_name_1 WHERE condition),

      column_name = (SELECT column_name FROM table_name_2 WHERE condition)

WHERE condition;

Example

Now we will update students_copy table by taking values  from students table.

select * from students_copy; 

Below is an update statement where the lastname for Barry is Allen is updated to Kara. The lastname column in the outer query is populated by subquery which refers to firstname column of students where roll no is 14.

update students_copy

set lastname=(select firstname from students where rollno=14)

where rollno=45;

commit;

Let us check the output after update.

select * from students_copy;