Sql Training - 4 & 5

 Use Arithmetic Operators 

CREATE TABLE regions (

region_id INT IDENTITY(1,1) PRIMARY KEY,

region_name VARCHAR (25) DEFAULT NULL

);


--DELETE CASCADE: When we create a foreign key using this option, it deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key.


--UPDATE CASCADE: When we create a foreign key using UPDATE CASCADE the referencing rows are updated in the child table when the referenced row is updated in the parent table which has a primary key.


CREATE TABLE countries (

country_id CHAR (2) PRIMARY KEY,

country_name VARCHAR (40) DEFAULT NULL,

region_id INT  NOT NULL,

FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE

);


CREATE TABLE locations (

location_id INT IDENTITY(1,1) PRIMARY KEY,

street_address VARCHAR (40) DEFAULT NULL,

postal_code VARCHAR (12) DEFAULT NULL,

city VARCHAR (30) NOT NULL,

state_province VARCHAR (25) DEFAULT NULL,

country_id CHAR (2) NOT NULL,

FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE

);


CREATE TABLE jobs (

job_id INT IDENTITY(1,1) PRIMARY KEY,

job_title VARCHAR (35) NOT NULL,

min_salary DECIMAL (8, 2) DEFAULT NULL,

max_salary DECIMAL (8, 2) DEFAULT NULL

);


CREATE TABLE departments (

department_id INT IDENTITY(1,1) PRIMARY KEY,

department_name VARCHAR (30) NOT NULL,

location_id INT  DEFAULT NULL,

FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE

);


CREATE TABLE employees1 (

employee_id INT IDENTITY(1,1) PRIMARY KEY,

first_name VARCHAR (20) DEFAULT NULL,

last_name VARCHAR (25) NOT NULL,

email VARCHAR (100) NOT NULL,

phone_number VARCHAR (20) DEFAULT NULL,

hire_date DATE NOT NULL,

job_id INT  NOT NULL,

salary DECIMAL (8, 2) NOT NULL,

manager_id INT  DEFAULT NULL,

department_id INT  DEFAULT NULL,

FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,

);

/*Data for the table regions */


INSERT INTO regions(region_name) VALUES ('Europe');

INSERT INTO regions(region_name) VALUES ('Americas');

INSERT INTO regions(region_name) VALUES ('Asia');

INSERT INTO regions(region_name) VALUES ('Middle East and Africa');



/*Data for the table countries */

INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','Belgium',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','Brazil',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','Canada',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','Switzerland',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','China',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','Germany',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','Denmark',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','Egypt',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','France',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','HongKong',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','Israel',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','India',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','Italy',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','Japan',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','US',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','UK',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','DE',4);


select * from countries

select * from locations


/*Data for the table locations */

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2014 Jabberwocky Rd','26192','Southlake','Texas','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2011 Interiors Blvd','99236','South San Francisco','California','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2004 Charade Rd','98199','Seattle','Washington','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2004 Charade Rd','98199','Seattle','Washington','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('8204 Arthur St',NULL,'London',NULL,'UK');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');


/*Data for the table jobs */

select * from jobs

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Public Accountant',4200.00,9000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Accounting Manager',8200.00,16000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Administration Assistant',3000.00,6000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('President',20000.00,40000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Administration Vice President',15000.00,30000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Accountant',4200.00,9000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Finance Manager',8200.00,16000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Human Resources Representative',4000.00,9000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Programmer',4000.00,10000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Marketing Manager',9000.00,15000.00);



/*Data for the table departments */


select * from locations


INSERT INTO departments(department_name,location_id) VALUES ('Administration',10);

INSERT INTO departments(department_name,location_id) VALUES ('Marketing',11);

INSERT INTO departments(department_name,location_id) VALUES ('Purchasing',12);

INSERT INTO departments(department_name,location_id) VALUES ('Human Resources',15);

INSERT INTO departments(department_name,location_id) VALUES ('Shipping',11);

INSERT INTO departments(department_name,location_id) VALUES ('IT',11);

INSERT INTO departments(department_name,location_id) VALUES ('Public Relations',12);

INSERT INTO departments(department_name,location_id) VALUES ('Sales',4);

INSERT INTO departments(department_name,location_id) VALUES ('Executive',7);

INSERT INTO departments(department_name,location_id) VALUES ('Finance',9);

INSERT INTO departments(department_name,location_id) VALUES ('Accounting',9);


select * from departments

select * from employees

/*Data for the table employees */



INSERT INTO employees1(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Steven','King','steven.king@sqltutorial.org','515.123.4567','1987-06-17',4,24000.00,NULL,24);

INSERT INTO employees1(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Neena','Kochhar','neena.kochhar@sqltutorial.org','515.123.4568','1989-09-21',5,17000.00,100,25);

INSERT INTO employees1(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Lex','De Haan','lex.de haan@sqltutorial.org','515.123.4569','1993-01-13',5,17000.00,100,26);

INSERT INTO employees1(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Alexander','Hunold','alexander.hunold@sqltutorial.org','590.423.4567','1990-01-03',9,9000.00,102,25);

INSERT INTO employees1(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Bruce','Ernst','bruce.ernst@sqltutorial.org','590.423.4568','1991-05-21',9,6000.00,103,24);

INSERT INTO employees1(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('David','Austin','david.austin@sqltutorial.org','590.423.4569','1997-06-25',9,4800.00,103,26);

What are Arithmetic Operators in SQL Server?

Arithmetic operators are used to performing mathematical calculations such as Addition, Subtraction, Multiplication, and Division on the given operand values. That means these operators are used to perform mathematical operations on two expressions of the same or different data types of numeric data. These different arithmetic operators are as follows:

  1. + (Addition Operator)
  2. – (Minus Operator)
  3. * (Multiplication Operator)
  4. / (Division Operator)
  5. % (Modulo Operator)
Arithmetic Operators Example:
SELECT 100 + 200 O/P = 300
SELECT 45 - 74 O/P = -29
SELECT 25 * 4 O/P = 100
SELECT 36 / 6 O/P = 6
SELECT 17 % 4 O/P = 1

Understand Operator Precedence 

SQL Operators

Every database administrator and user uses SQL queries for manipulating and accessing the data of database tables and views.

The manipulation and retrieving of the data are performed with the help of reserved words and characters, which are used to perform arithmetic operations, logical operations, comparison operations, compound operations, etc.

What is SQL Operator?

The SQL reserved words and characters are called operators, which are used with a WHERE clause in a SQL query. In SQL, an operator can either be a unary or binary operator. The unary operator uses only one operand for performing the unary operation, whereas the binary operator uses two operands for performing the binary operation


Learn the DESCRIBE command to display the table structure

--SELECT 17 % 4

SQL LOGICAL OPERATORS

The Logical Operator is nothing but which returns the result in one form, i.e., either it will display the query is true, or the query is false. The results displayed to combine or merge more than one true or false data.

The Logical Operators in SQL are as follows:

  1. SQL AND OPERATOR
  2. SQL OR OPERATOR
  3. SQL NOT OPERATOR
  4. SQL BETWEEN OPERATOR
  5. SQL IN OPERATOR
  6. SQL LIKE OPERATOR
SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary > 5000 AND salary < 7000

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary = 7000 OR salary = 8000

IS NULL

The IS NULL operator compares a value with a null value and returns true if the compared value is null; otherwise, it returns false.

For example, the following statement finds all employees who do not have a phone number:

SELECT 
    first_name, last_name, phone_number
FROM
    employees
WHERE
    phone_number IS NULL

BETWEEN

The BETWEEN operator searches for values that are within a set of values, given the minimum value and maximum value. Note that the minimum and maximum values are included as part of the conditional set.

For example, the following statement finds all employees whose salaries are between 9,000 and 12,000.

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary BETWEEN 9000 AND 12000

IN

The IN operator compares a value to a list of specified values. The IN operator returns true if the compared value matches at least one value in the list; otherwise, it returns false.

The following statement finds all employees who work in the department id 8 or 9.

SELECT 
    first_name, last_name, department_id
FROM
    employees1
WHERE
    department_id IN (24,25)

LIKE

The LIKE operator compares a value to similar values using a wildcard operator. SQL provides two wildcards used in conjunction with the LIKE operator:

  • The percent sign ( %) represents zero, one, or multiple characters.
  • The underscore sign ( _) represents a single character.

The following statement finds all employees whose first name starts with the string jo:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    first_name LIKE 'St%'

SELECT employee_id, first_name, last_name FROM employees1 WHERE first_name LIKE '_r%' SELECT employee_id, first_name, last_name FROM employees1 WHERE first_name LIKE '__u%'


SELECT employee_id, first_name, last_name, salary FROM employees1 WHERE department_id > 5 AND NOT salary > 10000


Equal to operator(=)

The equal to operator compares the equality of two expressions:

expression1 = expression2

Code language: SQL (Structured Query Language) (sql)

It returns true if the value of the left expression is equal to the value of the right expression; otherwise, it returns false.



SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    last_name = 'King';

Not equal to operator (<>)

The not equal to (<>) operator compares two non-null expressions and returns true if the value of the left expression is not equal to the right one; otherwise, it returns false.

expression1 <> expression2

Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns all employees whose department id is not 8.

SELECT 
    employee_id, first_name, last_name, department_id
FROM
    employees
WHERE
    department_id <> 8


Greater than operator (>)

The greater than operator (>) compares two non-null expressions and returns true if the left operand is greater than the right operand; otherwise, the result is false.

expression1 > expression2

Code language: SQL (Structured Query Language) (sql)

For example, to find the employees whose salary is greater than 10,000, you use the greater than operator in the WHERE clause as follows:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > 10000



Less than operator (<)

The less than operator compares two non-null expressions. The result is true if the left operand evaluates to a value that is lower than the value of the right operand; otherwise, the result is false.

The following shows the syntax of the less than operator:

expression1 < expression2

Code language: SQL (Structured Query Language) (sql)

For example, the statement below returns all employees whose salaries are less than 10,000:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary < 10000




Greater than or equal operator (>=)
The greater than or equal operator (>=) compares two non-null expressions. The result is true if the left expression evaluates to a value that is greater than the value of the right expression.
The following illustrates the syntax of the greater than or equal operator:
expression1 >= expression2

Code language: SQL (Structured Query Language) (sql)
For example, the following statement finds employees whose salaries are greater than or equal 9,000:
SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= 9000

------------------------


Describe the rules of precedence for comparison and logical operators 

Use character string literals in the WHERE clause 

Write queries that contain an ORDER BY clause to sort the output of a SELECT statement Sort output in descending and ascending order 

Usage of Single-Row Functions to Customize Output 

Describe the differences between single row and multiple row functions Manipulate strings with character function in the SELECT and WHERE clauses Manipulate numbers with the ROUND, TRUNC, and MOD functions 

Perform arithmetic with date data 

Manipulate dates with the DATE functions 


Comments

Post a Comment

Popular Posts