SQL, How To Select Columns, Rows And Join Tables

SQL, How To Select Columns, Rows And Join Tables

Selecting Specific Columns From a Table:

SELECT command, used to retrieve information from the database, allows you to select the columns to be including in a query. A query is a request for information, and one of the most common DB operations. Queries are performed by the SELECT command. A SELECT command must have at least two clauses; the first one is SELECT (the columns to retrieve) and second is FROM (the tables which contains those columns). You may place the clauses on the same line or on separate lines but always follow the last clause with a semicolon, e.g.

SELECT DNAME FROM DEPT:

This command displays the information in the department name column (DNAME) from the table DEPT.

You can display all the columns of a table, or only specific columns. For example, to select just the columns DNAME and DEPTNO from the table DEPT, enter:

SELECT DNAME DEPTNO FROM DEPT;

SQL will display the following data from the database

Selecting Specific Rows From a Table:

Suppose you want to select specific rows from any table’s database: for example, only the employees in Department 30 in the sample table EMP. To select rows from a table, you should include a WHERE clause in your SELECT commands. The WHERE clause must follow the FROM clause:

SELECT (columns) FROM (table) WHERE (certain conditions are met)

To list all information about employees in Department 30, enter:

SELECT FROM EMP WHERE ‘DEPTNO = 30’;

Joining Tables:

Rows in one table may be joined to rows in another table by common values in corresponding columns. For example, the sample tables EMP and DEPT both have a column, DEPTNO that contains dept number. These columns allow you to join rows in the two tables.

To make a query in which rows of two tables are joined, you must specify the join columns that contain corresponding information in the two tables. Specify the tables to be joined in the SELECT command’s FROM clause, and specify the join columns in the WHERE clause, When you join any two table like (DEPT to EMP), the join conditions have specific the relationship between them. Join conditions basically have two major types,

1. EQUI-JOINS
2. NON-EQUI-JOINS

SELECT (Columns) FROM (Tables, tables) WHERE (Logical expression);

The names of columns are specified in the usual way. Commas separate the names of tables. The logical expression specifies how the tables are to be joined; it refers to the join columns in the two tables, and its values are “true” for those combinations of rows that are to be joined. The WHERE clause is used to specify selection conditions as well as join columns. If the WHERE clause is used for both purposes, use AND to combine the logical expression that select rows with the ones that define join conditions.

To find Allan’s location, entered the command:

SELECT ENAME, LOC FROM EMP, DEPT WHERE ENAME = ‘Allen’ AND

EMP.DEPTNO = DEPT.DEPTNO;