T SQl Tutorials

When is Count Distinct

By using the DISTINCT keyword in a function called COUNT, we can return the number of different countries because you want to return only different values.

Select all records where the City column has the value 'Berlin' or 'London'.

select* FROM Customers where City = 'Berlin' = or City ' London';

Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.

select* from Customers where city ='Berlin' and Postalcode =12209:

Select all the different values from the Country column in the Customers table.

select Distinct Country FROM Customers;

What are Operators in The WHERE Clause?

= Equal > Greater than <Less than >=Greater than or equal to <=Less than or equal to <>Not equal. Note: In some versions of SQL this operator may be written as != BETWEEN Between a certain range LIKE Search for a pattern IN To specify multiple possible values for a column

What is a NULL Value?

A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

Delete all the records from the Customers table where the Country value is 'Norway'. create a syntax

Delete from Customers where Country = 'Norway';

For string values the ORDER BY keyword how do it will order the data and give a sample syntax

For string values the ORDER BY keyword how do it will order the data alphabetically: SELECT * FROM ProductsORDER BY ProductName;

Insert Multiple Rows It is also possible to insert multiple rows in one statement. To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

The SQL INSERT INTO Statement The INSERT INTO statement is used to insert new records in a table. INSERT INTO Syntax It is possible to write the INSERT INTO statement in two ways: 1. Specify both the column names and the values to be inserted:

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

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_nameVALUES (value1, value2, value3, ...); INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

Use Oracle to add ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.

SELECT * FROM Customers ORDER BY Customer Name DESC FETCH FIRST 3 ROWS ONLY;

Oracle statement that selects the first three records from the "Customers" table, where the country is "Germany"

SELECT * FROM Customers WHERE Country='Germany' FETCH FIRST 3 ROWS ONLY;

Create an Oracle statement selects the first 50% of the records from the "Customers" table?

SELECT * FROM Customers FETCH FIRST 50 PERCENT ROWS ONLY;

Use Mysql to add ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.

SELECT * FROM Customers ORDER BY Customer Name DESC LIMIT 3;

Mysql statement that selects the first three records from the "Customers" table, where the country is "Germany"

SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;

Using the oracle syntax create a statement to select the first 3 records of the customer table?

SELECT * FROM Customers FETCH FIRST 3 ROWS ONLY;

Using mysql syntax write a script to select the first 3 records of the customer table?

SELECT * FROM Customers LIMIT 3;

Using Both ASC and DESC The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

Select all records from a table where the City column has the value "Berlin".

SELECT * FROM Customers where city ="Berlin"

Write the Script to retrieve data from a table?

SELECT * FROM Customers; where customers is the name of the table

OR vs AND The OR operator displays a record if any of the conditions are TRUE. The AND operator displays a record if all the conditions are TRUE. he following SQL statement selects all fields from Customers where either City is "Berlin", CustomerName starts with the letter "G" or Country is "Norway":

SELECT * FROM CustomersWHERE City = 'Berlin' OR CustomerName LIKE 'G%' OR Country = 'Norway';

NOT IN Example Select customers that are not from Paris or London:

SELECT * FROM CustomersWHERE City NOT IN ('Paris', 'London');

AND vs OR The AND operator displays a record if all the conditions are TRUE. The OR operator displays a record if any of the conditions are TRUE. All Conditions Must Be True The following SQL statement selects all fields from Customers where Country is "Germany" AND City is "Berlin" AND PostalCode is higher than 12000:

SELECT * FROM CustomersWHERE Country = 'Germany'AND City = 'Berlin'AND PostalCode > 12000;

Combining AND and OR You can combine the AND and OR operators. The following SQL statement selects all customers from Spain that starts with a "G" or an "R". Make sure you use parenthesis to get the correct result. Example Select all Spanish customers that starts with either "G" or "R":

SELECT * FROM CustomersWHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');

Combining AND and OR You can combine the AND and OR operators. The following SQL statement selects all customers from Spain that starts with a "G" or an "R". Make sure you use parenthesis to get the correct result.

SELECT * FROM CustomersWHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%'); Without parenthesis, the select statement will return all customers from Spain that starts with a "G", plus all customers that starts with an "R", regardless of the country value:

NOT BETWEEN Example Select customers with a customerID not between 10 and 60:

SELECT * FROM CustomersWHERE CustomerID NOT BETWEEN 10 AND 60;

NOT LIKE Example Select customers that does not start with the letter 'A':

SELECT * FROM CustomersWHERE CustomerName NOT LIKE 'A%';

The NOT Operator The NOT operator is used in combination with other operators to give the opposite result, also called the negative result. In the select statement below we want to return all customers that are NOT from Spain: ExampleGet your own SQL Server Select only the customers that are NOT from Spain:

SELECT * FROM CustomersWHERE NOT Country = 'Spain'; SELECT column1, column2, ... FROM table_name WHERE NOT condition;

NOT Less Than Example Select customers with a CustomerID not less than 50:

SELECT * FROM CustomersWHERE NOT CustomerId < 50;

The SQL OR Operator The WHERE clause can contain one or more OR operators. The OR operator is used to filter records based on more than one condition, like if you want to return all customers from Germany but also those from Spain:

SELECT *FROM CustomersWHERE Country = 'Germany' OR Country = 'Spain'; SELECT column1, column2, ...FROM table_nameWHERE condition1 OR condition2 OR condition3 ...;

The WHERE clause can contain one or more OR operators. The OR operator is used to filter records based on more than one condition, like if you want to return all customers from Germany but also those from Spain: ExampleGet your own SQL Server Select all customers from Germany or Spain:

SELECT *FROM CustomersWHERE Country = 'Germany' OR Country = 'Spain'; SELECT column1, column2, ...FROM table_nameWHERE condition1 OR condition2 OR condition3 ...;

The SQL AND Operator and The WHERE clause can contain one or many AND operators. Use the WHERE and the AND operator to filter records based on more than one condition, like if you want to return all customers from Spain that starts with the letter 'G': create a syntax for this statement

SELECT *FROM CustomersWHERE Country = 'Spain' AND CustomerName LIKE 'G%'; SELECT column1, column2, ...FROM table_nameWHERE condition1 AND condition2 AND condition3 ...;

lists all customers with a value in the "Address" field

SELECT CustomerName, ContactName, AddressFROM CustomersWHERE Address IS NOT NULL;

Lists all customers with a NULL value in the "Address" field

SELECT CustomerName, ContactName, AddressFROM CustomersWHERE Address IS NULL;

what is the syntax when you Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result. For SQL Server and MS Access?

SELECT TOP 3 * FROM Customers ORDER BY Customer Name DESC;

Write a SQL statement that selects the first three records from the "Customers" table, where the country is "Germany" (for SQL Server/MS Access)?

SELECT TOP 3 * FROM Customers WHERE Country='Germany';

What is the syntax to select only the first 3 records of the Customers table?

SELECT TOP 3 * FROM Customers; SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;

Write a SQL statement which selects the first 50% of the records from the "Customers" table (for SQL Server/MS Access)

SELECT TOP 50 PERCENT * FROM Customers;

In Mysql what syntax to select a limited number of records?

SELECT column_name(s) FROM table_name WHERE condition LIMIT number;

What syntax will be used in Oracle 12 to fetch a number of records?

SELECT column_name(s) FROM table_nameORDER BY column_name(s) FETCH FIRST number ROWS ONLY;

IS NOT NULL Syntax

SELECT column_namesFROM table_nameWHERE column_name IS NOT NULL;

IS NULL Syntax

SELECT column_namesFROM table_nameWHERE column_name IS NULL;

What is T sql?

SQL is a standard language for storing, manipulating and retrieving data in databases.

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:

Use the NOT keyword to select all records from a table where City is NOT "Berlin".

Select * from Customers where not city = 'Berlin';

Select all records where the CustomerID column has the value 32.

Select *from Customers Where CustomerID = 32;

When is a sql delete statement used and give an example syntax?

The DELETE statement is used to delete existing records in a table. DELETE FROM table_name WHERE condition; Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

This is IS NOT NULL Operator?

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

What is this IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values). Always use IS NULL to look for NULL values.

When is ORDER BY keyword used?

The ORDER BY keyword is used to sort the result-set in ascending or descending order what is the sql syntax SELECT column1, column2, ...FROM table_nameORDER BY column1, column2, ... ASC|DESC;

How do the Order by Keyword sort data in Ascending or Descending order from your table

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. SELECT * FROM ProductsORDER BY Price DESC;

What is the The SQL SELECT TOP Clause used for?

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance. Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

What is the SQL SELECT DISTINCT Statement is used for

The SQL SELECT DISTINCT Statement Select all the different countries from the "Customers" table: SELECT DISTINCT Country FROM Customers;

What is the UPDATE Statement used for?

The UPDATE statement is used to update existing records in a table. Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

What is the sql where clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!

What will be your ORDER BY syntax when there are Several Columns you are pulling data from?

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName: SELECT * FROM CustomersORDER BY Country, CustomerName;

It is possible to delete all rows in a table without deleting the table?

This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name; The following SQL statement deletes all rows in the "Customers" table, without deleting the table: DELETE FROM Customers;

What is the order by Alphabetically DESC do to data in your table and give a sample syntax

To sort the table reverse alphabetically, use the DESC keyword: SELECT * FROM Products ORDER BY Product Name DESC;

What is the UPDATE Syntax?

UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

To delete the table completely, what syntax do you use?

Use the DROP TABLE statement: DROP TABLE Customers;