How can I Having Clouse?
|
HAVING cause is using for the show groups satisfying a criteria, SELECT * FROM employer GROUP BY dsgn HAVING pay > 10000; |
How Can I use IN Clause?
|
IN clause uses when we would like to get results if a field has any of the given value :
SELECT * FROM employer where name IN (\'Ramesh\',\'Ram\'); |
How can I count number of records in the table?
|
To count number of records in the table .For example we want to know How many employers are in the table :
SELECT COUNT(*) FROM employer; |
How can I retrieve a row with columns having a particular pattern?
|
To retrieve a row with columns having a particular pattern. For the Example pay of all those employees whose name starts with A :
SELECT pay FROM Employer WHERE name like \'A%\';
|
How can I retrieve selected columns of a particular row?
|
To retrieve selected columns of a particular row:
SELECT pay FROM employer WHERE name = \'Ram\'; |
How can I Insert some data into the created table.
|
When i would like to insert the data then we use to the \'INSERT INTO\' command followed by table name and values to be inserted.
> If we need to insert a row with values for all columns, then use the following command :
INSERT INTO employer VALUES(\'Ram\',\'MGR\',25,25000);
> If we need to insert values for selected columns only, then we need to specify those column names also in the command as shown :
INSERT INTO employer (NAME) VALUES (\'Ramesh\'); |
How You create the Table ?
|
For the create Table firstly we use \'CREATE TABLE\' command with name and data-type of each table field. We can also specify PRIMARY KEY and any other constraint like NOT NULL.
For example:
CREATE TABLE employer(NAME VARCHAR(80) PRIMARY NOT NULL,DSGN VARCHAR(5),AGE INTEGER,PAY INTEGER); |
How can I retrieve all columns of a particular row ?
|
To retrieve all columns of a particular row we can use where clause in sql
SELECT * FROM employer where name = \'Ramesh\'; |
How can I retrieve few columns of all rows?
|
To retrieve few columns of all rows:
SELECT name FROM employer; |
How can I Update the table?
|
We have a two ways for updating the table, Which is shown here :
Use UPDATE command with SET and name-value pairs like:
UPDATE Employer SET pay=40000;
For updating a particular row, Then We use WHERE clause in UPDATE command like :
UPDATE Employer SET pay = 25000 WHERE NAME= \'Ram\'; |
How can i short list to the table with resect to the column?
|
When we would like to get the sorted listed of the data with respect to any columns , Then we can use ORDER BY clause .
For the example we can shortlist of the table with respect to columns Name as shown :
SELECT * FROM employer ORDER BY name; |
How can I see the whole Table ?
|
When we would like to see our table then we Use SELECT command to retrieve the data.
SELECT * FROM employer; |
How can I Use AND/OR in WHERE clause to retrieve data based on multiple condition?
|
Use AND/OR in WHERE clause to retrieve data based on multiple condition.
SELECT * FROM employer WHERE name LIKE \'A%\' AND pay > 10000; |
How can I delete all records for using TRUNCATE command?
|
We can also delete all records using TRUNCATE command such as
TRUNCATE TABLE employer; |
How can I delete all records of the table ?
|
For delete all records, We can use :
DELETE FROM Employer; |
How can I delete a particular record in the Table ?
|
For delete a particular record, use DELETE command with WHERE clause like:
DELETE * FROM employer WHERE name=\'Ram\'; |
How can I set an alias for person table using few columns only?
|
For this query we can use AS command as defined below:
SELECT NAME,DSGN FROM Employer AS employees; |
How can I Add a column to the table?
|
When we add a columns in the table we can done through ALTER command like :
ALTER TABLE employer ADD exp INTEGER; |
How can I drop the column added in step 10 above?
|
When we want to be do for like this then we can use ALTER command with DROP like this :
ALTER TABLE employer DROP exp; |
How can I drop the created table?
|
When i wold like to drop the create table then we Use DROP TABLE command followed by table name.
DROP TABLE employer; |
How can I know Min value of numeric column in the table ?
|
Using the Min function, we can know the average value of a numeric column.
SELECT MIN(SAL) employer; |
How can I drop the database?
|
When we like to drop Database then we use DROP DATABASE command followed by database name :
DROP DATABASE emp; |
How can I know max value of numeric column in the table ?
|
Using the Max function, we can know the average value of a numeric column.
SELECT MAX(SAL) employer; |
How can I know Avg value of numeric column in the table ? |
Using the Avg function, we can know the average value of a numeric column.
SELECT Avg(SAL) employer; |
What do you mean by SELECT DISTINCT Statement in Sql?
|
Distinct Statement is basically use when in the table some of the columns may contain duplicate values.
It is not a problem .some time we want to be these type of values like :
If we have a any table , and we wold like to know the name of those persons who are from same place. that time we use this statement :
Syntax : SELECT DISTINCT column_name(s) FROM table_name
Example:SELECT DISTINCT City FROM employer; |
How can i use the TOP Clause in the SQL?
|
We are using Top clause(oracle) because if we like to know some limit of rows see in the output or some %age of the table we would like to show then we use this clause like :
> If we would like to see select only the two first records in the table above :
SELECT TOP 2 * FROM employer;
> If we would like to see select only 50% records in the table above :
SELECT TOP 50 PERCENT * FROM employer; |
How can i use [charlist] Wildcard?
|
Now we want to select the employer with a last name that starts with \"A\" or \"n\" or \"k\" from the \"employer\" table.
SELECT * FROM employer WHERE LastName LIKE \'[ank]%\'; |
How can I use _ Wildcard?
|
We want to select the employer with a first name that starts with any character, followed by \"Ka\" from the \"employer\" table.
SELECT * FROM employer WHERE FirstName LIKE \'_ka\';
Now we want to select the employer with a last name that starts with \"R\", followed by any character, followed by \"end\", followed by any character, followed by \"on\" from the \"employer\" table.
SELECT * FROM employer WHERE LastName LIKE \'R_end_on\'; |
How can i use the % Wildcard ?
|
This wildcard we use when we want to show which name who start from some special char like :
We want to select the persons living in a city that starts with \"Ka\" from the \"employer\" table.
SELECT * FROM employer WHERE City LIKE \'ka%\'; |
What the use of SQL Alias In sql query?
|
SQL Alias is the basically use for the make a easier to the query, I mean when you have a very long or complex table names or column names. Then we can use this function like :
Assume we have a table called \"Employer\" and another table called \"Product_Orders\". We will give the table aliases of \"e\' an \"po\" respectively. Now we would like to list all the orders that \"Modi groups\" is responsible for :
SELECT Statement With Alias:
SELECT po.OrderID, e.LastName, e.FirstName FROM Employer AS e ,Product_Orders AS p WHERE p.LastName=\'Modi\' WHERE p.FirstName=\'Groups\'
Now SELECT Statement with out Alias :
SELECT Product_Orders.OrderID, Employer.LastName, Employer.FirstName FROM Employer, Product_Orders WHERE Employer.LastName=\'Modi\' WHERE Emloyer.FirstName=\'Groups\';Now we can see that with alias query is so easy . |
How can use The INNER JOIN Keyword in Sql?
|
The INNER JOIN basically use for the return rows when there is at least one match in both tables.
Syntax : SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
How can i use FULL JOIN Keyword?
|
The FULL JOIN keyword is basically use for returning rows when there is a match in one of the tables.
Syntax :SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
What is the use of RIGHT JOIN Keyword?
|
In some databases RIGHT JOIN is called RIGHT OUTER JOIN. The RIGHT JOIN keyword is basically use for the Returning to the all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
Syntax :SELECT column_name(e) FROM table_name1RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
How can I use LEFT JOIN Keyword?
|
In some databases LEFT JOIN is called LEFT OUTER JOIN. The LEFT JOIN keyword is basically a use for the returning the all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
Syntax :SELECT column_name(s)FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
HOW can i use the ROUND() Function?
|
The ROUND() function is basically use for the round a numeric field to the number of decimals specified.
Syntax : SELECT ROUND(column_name,decimals) FROM table_name |
How can i use to the NOW() Function?
|
The NOW() function is using for the returning the current system date and time.
Syntax : SELECT NOW() FROM table_name |
How can i use to the FORMAT() Function?
|
The FORMAT() function is basically used to format how a field is to be displayed.
Syntax : SELECT FORMAT(column_name,format) FROM table_name |
what is the use of LEN() Function?
|
The LEN() function is basically use for the returning the value of length of the value in a text field.
Syntax : SELECT LEN(column_name) FROM table_name |
What is the use of MID() Function?
|
The MID() function is basically use for the extract the characters from the txt fields.
Syntax : SELECT MID(column_name,start[,length]) FROM table_name |
How can I use the UNION Operator?
|
The UNION operator is used to combine the result-set of two or more SELECT statements.Each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types. Also the columns in each SELECT statement must be in the same order.
Syntax for UNION : SELECT column_name(e) FROM Table_name1 UNION SELECT column_name(e) FROM table_name2
SQL UNION ALL SyntaxSELECT column_name(e) FROM table_name1 UNION ALL SELECT column_name(e) FROM table_name2 |
What is the use of LCASE() Function?
|
The LCASE() function is basically use for the converts the field to Lowercase.
Syntax : SELECT UCASE(column_name) FROM table_name |
What is the use of LAST() Function?
|
The LAST() function is mainly use for the returning the Last value of the selected column.
Syntax : SELECT FIRST(column_name) FROM table_name |
What is the use of UCASE() Function?
|
The UCASE() function is basically use for the convert the value of a field to uppercase.
Syntax: SELECT UCASE(column_name) FROM table_name |
HOw can i create the Database?
|
DATABASE statement is basically crate the CREATE DATABASE Command.
Syntax : CREATE DATABASE database_name |
How can i use INTO Statement ?
|
The SQL SELECT INTO statement basically for the use in many fields and It can be used to create backup copies of tables.
The SELECT INTO statement is use for the selects data from one table and It can also be inserts it into a different table.
The SELECT INTO statement is mostly use for the backup copies of tables.
Syntax : SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
We can select only the columns we want into the new table :
SELECT column_name(e)I NTO new_table_name [IN externaldatabase] FROM old_tablename |
How can I create the Index?
|
The CREATE INDEX statement is basically use for the create indexes in tables. Its mainly Indexes allow the database application to find data fast, without reading the whole table.
Syntax : CREATE INDEX index_name ON table_name (column_name)
When we would like to Unique Index then syntax,but its have not allowed duplicate :
Syntax : CREATE UNIQUE INDEX index_name ON table_name (column_name) |
What is the use of FIRST() Function?
|
The FIRST() function is used to the returning the first value of the selected column.
Syntax : SELECT FIRST(column_name) FROM table_name |
How do we use NOT NULL Constraint?
|
Not NUll Constraints is basically means that we can\'t insert a new record, or also cant be update a record without adding a value to this field. The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value.
For the example: The following SQL enforces the \"E_Id\" column and the \"LastName\" column to not accept NULL values :
CREATE TABLE Employer(E_Id int NOT NULL,LastName varchar(255)NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255)) |
How can I get the sum of a column?
|
To get the sum of a column .For example we want to know total pay to be paid :
SELECT SUM(PAY) FROM employer; |
How can I got group the results?
|
To group the results, use GROUP BY as in following:
SELECT * FROM employer GROUP BY deprt; |
