SQL NOW() Function

By -

The NOW() Function

The NOW() function returns the current system date and time.

image

SQL NOW() Example

We have the following "Products" table:

image

image

Now we want to display the products and prices per today's date. We use the following SELECT statement:

image

SQL FORMAT() Function

The FORMAT() Function

The FORMAT() function is used to format how a field is to be displayed.

image

SQL FORMAT() Example

We have the following "Products" table:

image

Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").

We use the following SELECT statement:

image

The result-set will look like this:

image

SQL Quick Reference

SQL Statement

Syntax

AND / OR

SELECT column_name(s)

FROM table_name WHERE condition AND|OR condition

ALTER TABLE

ALTER TABLE table_name

ADD column_name datatype or

ALTER TABLE table_name DROP COLUMN column_name

AS (alias)

SELECT column_name AS column_alias

FROM table_name or

SELECT column_name

FROM table_name AS table_alias

BETWEEN

SELECT column_name(s)

FROM table_name WHERE column_name

BETWEEN value1 AND value2

CREATE DATABASE

CREATE DATABASE database_name

CREATE TABLE

CREATE TABLE table_name

(

column_name1 data_type, column_name2 data_type, column_name2 data_type,

...

)

CREATE INDEX

CREATE INDEX index_name

ON table_name (column_name) or

CREATE UNIQUE INDEX index_name ON table_name (column_name)

CREATE VIEW

CREATE VIEW view_name AS

SELECT column_name(s) FROM table_name WHERE condition

DELETE

DELETE FROM table_name

WHERE some_column=some_value or

DELETE FROM table_name (Note: Deletes the entire table!!)

DELETE*FROMtable_name (Note: Deletes the entire table!!)

DROP DATABASE

DROP DATABASE database_name

DROP INDEX

DROP INDEX table_name.index_name (SQL Server)

DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle)

ALTER TABLE table_name

DROP INDEX index_name (MySQL)

DROP TABLE

DROP TABLE table_name

GROUP BY

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value GROUP BY column_name

HAVING

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value GROUP BY column_name

HAVING aggregate_function(column_name) operator value

IN

SELECT column_name(s)

FROM table_name WHERE column_name IN (value1,value2,..)

INSERT INTO

INSERT INTO table_name

VALUES (value1, value2, value3,....)

or

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)

INNER JOIN

SELECT column_name(s)

FROM table_name1 INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

LEFT JOIN

SELECT column_name(s)

FROM table_name1 LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

RIGHT JOIN

SELECT column_name(s)

FROM table_name1 RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

FULL JOIN

SELECT column_name(s)

FROM table_name1 FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

LIKE

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern

ORDER BY

SELECT column_name(s)

FROM table_name

ORDER BY column_name [ASC|DESC]

SELECT

SELECT column_name(s)

FROM table_name

SELECT *

SELECT *

FROM table_name

SELECT DISTINCT

SELECT DISTINCT column_name(s)

FROM table_name

SELECT INTO

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_table_name

or

SELECT column_name(s)

INTO new_table_name [IN externaldatabase] FROM old_table_name

SELECT TOP

SELECT TOP number|percent column_name(s)

FROM table_name

TRUNCATE TABLE

TRUNCATE TABLE table_name

UNION

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

UNION ALL

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

UPDATE

UPDATE table_name

SET column1=value, column2=value,... WHERE some_column=some_value

WHERE

SELECT column_name(s)

FROM table_name

WHERE column_name operator value

Comments

Post a Comment

Popular posts from this blog

Concurrency Control:Shadow Paging

By -
Image
Shadow Paging An alternative to log-based crash-recovery techniques is shadow paging . The shadow-paging technique is essentially an improvement on the shadow-copy technique that we saw in Section 15.3. Under certain circumstances, shadow paging may require fewer disk accesses than do the log-based methods discussed previously. There are, however, disadvantages to the shadow-paging approach, as we shall see, that limit its use. For example, it is hard to extend shadow paging to allow multiple transactions to execute concurrently. As before, the database is partitioned into some number of fixed-length blocks, which are referred to as pages . The term page is borrowed from operating systems, since we are using a paging scheme for memory management. Assume that there are n pages, numbered 1 through n . (In practice, n may be in the hundreds of thousands.) These pages do not need to be stored in any particular order on disk (there are many reasons why they do not, as we saw in Chapter 1...
Read more

Choice of Evaluation Plans

By -
Image
Choice of Evaluation Plans Generation of expressions is only part of the query-optimization process, since each operation in the expression can be implemented with different algorithms. An evaluation plan is therefore needed to define exactly what algorithm should be used for each operation, and how the execution of the operations should be coordinated. Figure 14.4 illustrates one possible evaluation plan for the expression from Figure 14.3. As we have seen, several different algorithms can be used for each relational operation, giving rise to alternative evaluation plans. Further, decisions about pipelining have to be made. In the figure, the edges from the selection operations to the merge join operation are marked as pipelined; pipelining is feasible if the selection operations generate their output sorted on the join attributes. They would do so if the indices on branch and account store records with equal values for the index attributes sorted by branch-name . Interaction of Eva...
Read more

Entity-Relationship Model part2

By -
Image
Refining the COMPANY database schema by introducing relationships By examining the requirements, six relationship types are identified. All are binary relationships( degree 2) Listed below with their participating entity types: WORKS_FOR (between EMPLOYEE, DEPARTMENT) MANAGES (also between EMPLOYEE, DEPARTMENT) CONTROLS (between DEPARTMENT, PROJECT) WORKS_ON (between EMPLOYEE, PROJECT) SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor)) DEPENDENTS_OF (between EMPLOYEE, DEPENDENT) ER DIAGRAM – Relationship Types are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF R elationship Types In the refined design, some attributes from the initial entity types are refined into relationships: Manager of DEPARTMENT -> MANAGES Works_on of EMPLOYEE -> WORKS_ON Department of EMPLOYEE -> WORKS_FOR etc In general, more than one relationship type can exist between the same participating entity types MANAGES and WORKS_FOR are distinct relationshi...
Read more