Working with SQL in displaying number values

Displaying and working with Number values:

SQL normally displays numbers with as many digits as required for accuracy, up to a standard display width of 10. But you can choose a different format for any number column by using a format model in a column command. The COLUMN command identifies the column you want to format and the model you want to use:

Column Format model,

To make a query that displays SAL in dollar format, enter:

COLUMN SAL FORMAT $99.999

SELECT ENAME, SAL COMM FROM EMP WHERE JOB = ‘Salesman’

Arithmetic Expressions: You perform a calculation based on numbers in the DB by including an arithmetic expression in a SQL command. An arithmetic expression consists of numbers, column names, and number values connected by arithmetic operators. You may use these operators in an arithmetic expression:

+ ADD, – Subtract, * Multiply, / Divide

SELECT ENAME, SAL, COMM, SAL + COMM FROM EMP

WHERE JOB= ‘salesman’;

Although SAL + COMM is not a column in the EMP table, still SQL displays the calculated result as it is. To calculate total annual compensation for all sales people, enter:

SELECT ENAME, SAL, COMM, 12* (SAL+COMM) FROM EMP

WHERE JOB = ‘Salesman’

If you omit the parentheses, the multiplication will be performed before the addition.

Displaying and working with CHAR values:

Char values are normally displayed exactly as they were entered, but you can change the displayed width of a char column by using the COLUMN command with a format model consisting of the letter. A (for alphanumeric) followed by the width of the column in character

COLUMN column format model;

To set the width of the column DNAME to eight characters and make a query, enter:

COLUMN DNAME FORMAT A8;

SELECT * FROM DEPT;

To change the column’s width and repeat the query, enter:

COLUMN DNME FORMAT A14;

RUN;

SELECT = FROM DEPT;

As the first query shows, if the contents of a field overflow the column width, the value wraps over the next line. You can truncate (cut off) the field instead by using the COLUMN TRUNC commands.

Displaying and working with Date values:

When you enter date values in your tables, you can make use of SQL knowledge about dates. SQL knows the names and abbreviations of months, the number of days in each month, even the day of the week for any date between the year 4712 BC and 4712 AD.

Date values are normally displayed in a standard format that looks like this:

•  12-JAN –02 up to ORACLE 7.0 & MS-SQL but 12-JAN-2005 in later versions

This format is written ‘DD-MM-YY’. Whenever you display a date field without specifying a format, it is displayed in this format.

To display employees’ hire dates in the standard format, enter:

SELECT ENAME, HIREDATE FROM EMP WHERE DEPTNO = 20;

To display employees’ hire dates in a format like 01/15/83, enter:

COLUMN HIRE_DATE FORMAT A9;

SELECT ENAME, TO CHAR (‘HIREDATE, MM/DD/YY’) HIRE_DATE

FROM EMP WHERE DEPTNO = 20;

• COLUMN HIRE_DATE FORMAT A9;
• SELECT ENAME, TO_CHAR (HIREDATE, MM/DD/YY’) HIRE_DATE

FROM EMP WHERE DEPTNO = 20;

Using Date Values in SQL Commands:

Most of the date columns in SELECT clauses, enable you to use a date value in any SQL clause where you may use a number or char value.

To display employees hired between January 4, 2006 and April 15, 2005, in order of seniority, enter:

SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE BETWEEN’

4-JAN-2005’ AD 15-APR-2005 ORDER BY HIREDATE;

Displaying a working query or list with Null values:

Not all the fields in the DB need to contain values; a data field without a value in it is said to contain a null value. A null value is appropriate in two situations:

•     Where a value is unknown
•     Where a value is not meaningful

A number field containing a null value is different from one containing a value of zero. Null values are normally displayed as blanks and zero values as numeric zeroes (0). For example, when displaying the employees in Department 30 of the sample company, you will see some blank spaces in the commission (COMM) column.

SELECT * FROM EMP WHERE DEPTNO = 30;

Searching For Null Values:

To search for rows that contain null values in a field, use a WHERE clause containing the condition IS NULL. To display information about all employees who are not eligible to receive commissions, enter:

SELECT ENAME, SAL, COMM, JOB, FROM EMP WHERE COMM IS NULL;

Creating A Table:

Suppose you want to expand the samples DB to keep track of projects in addition to departments and employees. You want to create a project table.

To create a table, use the CREATE TABLE command, specify:

•          The name of the table
•          The name of each column
•          The type of data to be stored in each column
•          The width of each column

Before creating a new table, you should make a layout of this information, in your mind or a paper.

To create the Project table, enter:

CREATE TABLE PROJECT (PROJNO NUMBER (7) NOT NULL, PNAME CHAR (10), BUDGET NUMBER (3, 2));………… (Note: Table created)

The message Table created tells you that the table is now part of the Database.

1.      The clause CREATE TABLE PROJECT

2.      A list of columns, which specifies the name, data type, and format of each column

(PROJNO NUMBER (7) NOT NULL…).

Valid and Invalid Table Names:

Some Specifications and Their Menings:

Insert, update, and deleting Row into a Table:

The INSERT command insets to one or more rows into a table, its format should be: Suppose a new employee named CHARTER has been hired and you want to add her record to the sample EMP table.

INSERT INTO table VALUES (a list of data values);

INSERT INTO EMP VALUES (7954, ‘CHARTER’, ‘CLERK’, ‘7-APR-2010’,

1000, NULL, 30);……….. Note (One record created).

SELECT FROM EMP WHERE ENAME = ‘CARTER’;

The UPDATE command consists of an UPDATE clause followed by a SET clause and an optional WHERE clause:UPDATE sets each field equal to the corresponding value in the row selected by the logical expression. To update Ward’s data and verify the update, enter:

• UPDATE table SET field = value, field = value…. WHERE logical expression;
• UPDATE EMP SET JOB = ‘Salesman’, HIREDATE = SYSDATE WHERE ENAME = ‘Ward’;………… (Note: Record Updated)
• SELECT * FROM EMP WHERE ENAME = ‘Ward’;

The DELETE command contains a FROM clause followed by an optional WHERE clause Suppose Ward is no longer eligible for bonuses. Use the DELETE command to remove ward’s record from the BOUNS table.

DELETE FROM table WHERE logical expression;

To delete ward from the BOUNS table, enter:

DELETE FROM BOUNS WHERE ENAME = ‘Ward’… (Note: One record deleted)

Modifying a Table:

As your information needs change, some of your table may need to be modified. For example, you may wish to enlarge a five –digit zip code column to nine digits, the ALTER table command, enables you to change the format of an existing table.

•          Enlarge a column
•          Add a column

Suppose the budget for project 103 in the sample company is to be updated to $1,500,000. You must update the PROJECT table to change this. But the BUDGET column was defined as NUMBER (8, 2); therefore the largest number it can hold is $999,999.99.

To increase the maximum width of a CHAR or NUMBER field, use the ALTER table command with a MODIFY clause.

•  ALTER TABLE table MODIFY (Column definitions);
•  ALTER TABLE PROJECT MODIFY (BUDGET NUMBER (8,2)). (Note; Table altered)

Now you can give PROJECT 103 a budget of $1,500,000;

•  UPDATE PROJECT SET BUDGET = 1500000

WHERE PROJNO = 103;…………….(Note: one record updated).

Using Sub-Queries:

A sub-query is a query that is used in clause of another SQL in ORACLE command. Sub–queries enable you to build powerful and compelling commands out of simple ones.

Sub-queries are most often used in the WHERE clause of a SELECT command (the main query). Sub-queries are useful when you want to select rows from a table with a condition that depends on data in the table itself.

Suppose you want to find the employees who have the same job as JONES. You might do this with two queries, one to find JONES job and others to find all the employees with same job.

• SELECT JOB FROM EMP WHERE ENAME = ‘JONES’;

Now you know that Jones is a Manager, you can make a second query to find all the managers.

•   SELECT ENAME, JOB FROM EMP WHERE JOB = ‘MANAGER’;

You can reach the same result with one query by using a sub-query in the WHERE clause to find Jones’ job. Always enclose sub-queries in parentheses.

• SELECT ENAME, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = ‘JONES’);