Goal Identify SQL commands used to manage your database Step 1. SQL Commands The

Question

Goal

Identify SQL commands used to manage your database

Step 1. SQL Commands

There are about 100 SQL commands that are commonly used. You have learned about SELECT, INSERT, UPDATE and DELETE. W3Schools shows you on SQL at http://www.w3schools.com/SQl/default.asp (Links to an external site.)Links to an external site..

Explain what each of these commands do and explain the syntax.

Provide a sample that would work with your database.

In your reply post:

Check and see if the sample syntax was correct. If not, provide the solution.

Did they use the commands appropriately?

How can you use these tools to backup your data?

Explanation / Answer

INNER JOIN:

INNER join command retrieves matching values from two tables.

Simple syntax would be:

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

LIKE:

LIKE operator used to serach for patricular pattern in a column.

Simple Syntax would be:

SELECT col1, col2 FROM table_name WHERE columnn LIKE pattern;

ex: SELECT NAME FROM EMPLOYEE WHERE NAME="M%" //Finds any values that starts with "M".

Here this commands retrives names starts with 'M'. For example 'Murali'.

ALIASES: Aliase give temporary name to the given Column of a table. It is temporary only available till the execution of the command.

Simple synstax:

SELECT Col_name AS alias_name
FROM table_name;

SELECT FIRST_NAME AS NAME FROM STUDENT;

IN:

IN clause is used to specify multiple values in where condition.

Simple syntax would be:

SELECT Col_name(s)
FROM table_name
WHERE Col_name IN (val1, val2);

SELECT NAME FROM EMPLOYEE WHERE ID IN(123,124);

Here only the names of student id's 123 and 124 retrieved.

BETWEEN:
To select value within range we use between. For example, Only to retrieve the students of their age between specific range.

Simple syntax would be:

SELECT Col_name(s)
FROM table_name
WHERE Col_name BETWEEN value1 AND value2;

SELECT NAME
FROM STUDENT
WHERE AGE BETWEEN 10 AND 25;

Display students of their age between 10 and 25.

SELECT INTO:

SELECT INTO copies the data from one table to other table.

Simple syntax:

SELECT * INTO newtable FROM oldtable WHERE condition;

INSERT INTO SELECT:

INSERT INTO SELECT command selects the data from one table and inserts into another table.

Simple syntax:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;

CREATE TABLE:

Used to create a new table in database.

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype...);

Example:

CREATE TABLE STUDENT (NAME VARCHAR,ID INTEGER,AGE INTEGER);

PRIMARY KEY:
Primary Key command constraint makes user to enter only unique records. It does not contain NULL values. For example, student id must be unique and should not accept null.

Example:
CREATE TABLE STUDENT(ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255), Age int,PRIMARY KEY (ID));

FOREIGN KEY:
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

DROP:

Drop table to delete existing table in database.
DROP TABLE TABLENAME;

Example:

DROP TABLE STUDENT;

ALTER:

ALTER table used to add,modify,delete existing table.
simple syntax:
ALTER TABLE table_name ADD column_name datatype;

Example:

ALTER TABLE STUDENT ADD GRADE VARCHAR;

Here GRADE column is added to the STUDENT table.

AVG: It returns the average value of the numeric value.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

SELECT AVG(AGE)
FROM STUDENT

COUNT:
Count function used to return number of rows with condition specified in WHERE condition.
Syntax:
SELECT COUNT(column_name) FROM table_name WHERE condition;

MAX AND MIN:
MAX and MIN function used return maximum value and minimum value from selected column.

syntsx:
SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

GROUP BY:
The GROUP BY statement is often used to group the result-set by one or more columns.

Syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s);

Example:

SELECT NAME FROM STUDENT GROUP BY ID;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.