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

Database System Architectures:Parallel Systems.

By -
Image
Parallel Systems Parallel systems improve processing and I/O speeds by using multiple CPUs and disks in parallel. Parallel machines are becoming increasingly common, making the study of parallel database systems correspondingly more important. The driving force behind parallel database systems is the demands of applications that have to query extremely large databases (of the order of terabytes — that is, 1012 bytes) or that have to process an extremely large number of transactions per second (of the or- der of thousands of transactions per second). Centralized and client – server database systems are not powerful enough to handle such applications. In parallel processing, many operations are performed simultaneously, as opposed to serial processing, in which the computational steps are performed sequentially. A coarse-grai n parallel machine consists of a small number of powerful processors; a massivel y parallel or fine-grai n parallel machine uses thousands of smaller processors....
Read more

Summary of Query Optimization

By -
Image
Summary • Given a query, there are generally a variety of methods for computing the answer. It is the responsibility of the system to transform the query as entered by the user into an equivalent query that can be computed more efficiently. The process of finding a good strategy for processing a query, is called query optimization . • The evaluation of complex queries involves many accesses to disk. Since the transfer of data from disk is slow relative to the speed of main memory and the CPU of the computer system, it is worthwhile to allocate a considerable amount of processing to choose a method that minimizes disk accesses. • The strategy that the database system chooses for evaluating an operation de- pends on the size of each relation and on the distribution of values within columns. So that they can base the strategy choice on reliable information, database systems may store statistics for each relation r . These statistics include The number of tuples in the relation r The...
Read more

Join Operation

By -
Image
Join Operation In this section, we study several algorithms for computing the join of relations, and we analyze their respective costs. We use the term equi-joi n to refer to a join of the form r r . A = s. B s , where A and B are attributes or sets of attributes of relations r and s respectively. We use as a running example the expression Nested-Loop Join Figure 13.4 shows a simple algorithm to compute the theta join, r s , of two rela tions r and s . This algorithm is called the nested-loop join algorithm, since it basi cally consists of a pair of nested for loops. Relation r is called the outer relation and relation s the inner relation of the join, since the loop for r encloses the loop for s . The algorithm uses the notation t r · t s , where t r and t s are tuples; t r · t s denotes the tuple constructed by concatenating the attribute values of tuples t r and t s . Like the linear file-scan algorithm for selection, the nested-loop join algorithm re quires no indices,...
Read more