Set up a local MySQL server effortlessly with this guide, perfect for practicing MySQL scripts without the need for an external server. Simply install XAMPP and DBeaver, and you're ready to run your SQL queries and hone your skills as much as you desire.
➡️ Download Apps
➡️ Start Xampp App
➡️ Start Dbeaver App
➡️ Create Table
➡️ Insert to Table
➡️ Execute the code
➡️ HR database(Create Table -Insert data into Table)
➡️ SQL Query Examples
➡️ Run Query with IntelliJ
➡️ Run Query with Vscode
📂 To download Xampp CLICK HERE
📂 To download Dbeaver CLICK HERE
📂 To download Vscode CLICK HERE
📂 To download IntelliJ CLICK HERE
START XAMPP CONTORL : make sure to click on "Start" in front of Apache and MySQL.
Now, your local server is running.
Now, you should have a screen that looks like this:
✔️ Create connection:
✔️ Click on the MySQL database.
Keep the settings as shown in the image: the username is "root," no password is set, and the port is 3306.
If prompted to download, click on the download option.
Right-click on the database and choose the option to create a new database.
after you create database right click on your database and SQL editor then Open SQL script
This provides a method to initiate the creation of tables, insert data into them, and retrieve the stored information:
If we intend to create the table by typing the script, it is a good practice to do so.
The MySQL CREATE TABLE Statement:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);for example we can type this script :
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
And run the code.
After executing the code, you will observe this.
It's time to populate the table with data using the MySQL INSERT INTO statement
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);For example :
INSERT INTO Persons (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');I insert this data in our table :
INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES ('12', 'Johnson', 'David', '123 St', 'Atlanta');
Execute the code, and you'll successfully add our initial data into the table.
We can now fetch the data:
SELECT * FROM PERSONS; 
------> Another method is to open your web browser and click on "Admin" next to MYSQL in the XAMPP app.
Now you can select your database :
✔️ You can establish the table here and specify the number of columns.
✔️ The Insert tab allows you to input data into your table.
✔️ In the Structure tab, you have the flexibility to modify your table's Name, Type, Collation, Attributes, Null settings, and add or remove elements from the table
✔️ SQL tab let us to type our queries :
After typing, press and hold Ctrl + Enter on your keyboard, or click the 'Go' button at the bottom of the screen:
And it will lead you to a screen displaying the results:
In this scenario, DBeaver is not required either.
✔️ add HR database to our local host:
✔️ the souce of these HR schema comes from github page SourecCode for refrence purpose.
right-click on your database and choose SQL Editor / new SQL Script :
copy and past these tables and run one by one!
CREATE TABLE regions (
region_id INT (11) UNSIGNED NOT NULL,
region_name VARCHAR(25),
PRIMARY KEY (region_id)
);
CREATE TABLE countries (
country_id CHAR(2) NOT NULL,
country_name VARCHAR(40),
region_id INT (11) UNSIGNED NOT NULL,
PRIMARY KEY (country_id)
);
CREATE TABLE locations (
location_id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
street_address VARCHAR(40),
postal_code VARCHAR(12),
city VARCHAR(30) NOT NULL,
state_province VARCHAR(25),
country_id CHAR(2) NOT NULL,
PRIMARY KEY (location_id)
);
CREATE TABLE departments (
department_id INT (11) UNSIGNED NOT NULL,
department_name VARCHAR(30) NOT NULL,
manager_id INT (11) UNSIGNED,
location_id INT (11) UNSIGNED,
PRIMARY KEY (department_id)
);
CREATE TABLE jobs (
job_id VARCHAR(10) NOT NULL,
job_title VARCHAR(35) NOT NULL,
min_salary DECIMAL(8, 0) UNSIGNED,
max_salary DECIMAL(8, 0) UNSIGNED,
PRIMARY KEY (job_id)
);
CREATE TABLE employees (
employee_id INT (11) UNSIGNED NOT NULL,
first_name VARCHAR(20),
last_name VARCHAR(25) NOT NULL,
email VARCHAR(25) NOT NULL,
phone_number VARCHAR(20),
hire_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
salary DECIMAL(8, 2) NOT NULL,
commission_pct DECIMAL(2, 2),
manager_id INT (11) UNSIGNED,
department_id INT (11) UNSIGNED,
PRIMARY KEY (employee_id)
);
CREATE TABLE job_history (
employee_id INT (11) UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
department_id INT (11) UNSIGNED NOT NULL
);
ALTER TABLE job_history ADD UNIQUE INDEX (
employee_id,
start_date
);
CREATE VIEW emp_details_view
AS
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.country_id,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.job_title,
l.city,
l.state_province,
c.country_name,
r.region_name
FROM employees e,
departments d,
jobs j,
locations l,
countries c,
regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id;After you run all the table thats mean you create all table that we need you can save and rename this script like this:
again right-click on your database and choose SQL Editor / new SQL Script :
copy and past these insertion and run them.
easy way to run all at one time is to select all of them then click on thrid run option is like paper has play shape at middle (Execute SQL Script)
INSERT INTO regions
VALUES (
1,
'Europe'
);
INSERT INTO regions
VALUES (
2,
'Americas'
);
INSERT INTO regions
VALUES (
3,
'Asia'
);
INSERT INTO regions
VALUES (
4,
'Middle East and Africa'
);
COMMIT;
INSERT INTO countries
VALUES (
'IT',
'Italy',
1
);
INSERT INTO countries
VALUES (
'JP',
'Japan',
3
);
INSERT INTO countries
VALUES (
'US',
'United States of America',
2
);
INSERT INTO countries
VALUES (
'CA',
'Canada',
2
);
INSERT INTO countries
VALUES (
'CN',
'China',
3
);
INSERT INTO countries
VALUES (
'IN',
'India',
3
);
INSERT INTO countries
VALUES (
'AU',
'Australia',
3
);
INSERT INTO countries
VALUES (
'ZW',
'Zimbabwe',
4
);
INSERT INTO countries
VALUES (
'SG',
'Singapore',
3
);
INSERT INTO countries
VALUES (
'UK',
'United Kingdom',
1
);
INSERT INTO countries
VALUES (
'FR',
'France',
1
);
INSERT INTO countries
VALUES (
'DE',
'Germany',
1
);
INSERT INTO countries
VALUES (
'ZM',
'Zambia',
4
);
INSERT INTO countries
VALUES (
'EG',
'Egypt',
4
);
INSERT INTO countries
VALUES (
'BR',
'Brazil',
2
);
INSERT INTO countries
VALUES (
'CH',
'Switzerland',
1
);
INSERT INTO countries
VALUES (
'NL',
'Netherlands',
1
);
INSERT INTO countries
VALUES (
'MX',
'Mexico',
2
);
INSERT INTO countries
VALUES (
'KW',
'Kuwait',
4
);
INSERT INTO countries
VALUES (
'IL',
'Israel',
4
);
INSERT INTO countries
VALUES (
'DK',
'Denmark',
1
);
INSERT INTO countries
VALUES (
'HK',
'HongKong',
3
);
INSERT INTO countries
VALUES (
'NG',
'Nigeria',
4
);
INSERT INTO countries
VALUES (
'AR',
'Argentina',
2
);
INSERT INTO countries
VALUES (
'BE',
'Belgium',
1
);
COMMIT;
INSERT INTO locations
VALUES (
1000,
'1297 Via Cola di Rie',
'00989',
'Roma',
NULL,
'IT'
);
INSERT INTO locations
VALUES (
1100,
'93091 Calle della Testa',
'10934',
'Venice',
NULL,
'IT'
);
INSERT INTO locations
VALUES (
1200,
'2017 Shinjuku-ku',
'1689',
'Tokyo',
'Tokyo Prefecture',
'JP'
);
INSERT INTO locations
VALUES (
1300,
'9450 Kamiya-cho',
'6823',
'Hiroshima',
NULL,
'JP'
);
INSERT INTO locations
VALUES (
1400,
'2014 Jabberwocky Rd',
'26192',
'Southlake',
'Texas',
'US'
);
INSERT INTO locations
VALUES (
1500,
'2011 Interiors Blvd',
'99236',
'South San Francisco',
'California',
'US'
);
INSERT INTO locations
VALUES (
1600,
'2007 Zagora St',
'50090',
'South Brunswick',
'New Jersey',
'US'
);
INSERT INTO locations
VALUES (
1700,
'2004 Charade Rd',
'98199',
'Seattle',
'Washington',
'US'
);
INSERT INTO locations
VALUES (
1800,
'147 Spadina Ave',
'M5V 2L7',
'Toronto',
'Ontario',
'CA'
);
INSERT INTO locations
VALUES (
1900,
'6092 Boxwood St',
'YSW 9T2',
'Whitehorse',
'Yukon',
'CA'
);
INSERT INTO locations
VALUES (
2000,
'40-5-12 Laogianggen',
'190518',
'Beijing',
NULL,
'CN'
);
INSERT INTO locations
VALUES (
2100,
'1298 Vileparle (E)',
'490231',
'Bombay',
'Maharashtra',
'IN'
);
INSERT INTO locations
VALUES (
2200,
'12-98 Victoria Street',
'2901',
'Sydney',
'New South Wales',
'AU'
);
INSERT INTO locations
VALUES (
2300,
'198 Clementi North',
'540198',
'Singapore',
NULL,
'SG'
);
INSERT INTO locations
VALUES (
2400,
'8204 Arthur St',
NULL,
'London',
NULL,
'UK'
);
INSERT INTO locations
VALUES (
2500,
'Magdalen Centre, The Oxford Science Park',
'OX9 9ZB',
'Oxford',
'Oxford',
'UK'
);
INSERT INTO locations
VALUES (
2600,
'9702 Chester Road',
'09629850293',
'Stretford',
'Manchester',
'UK'
);
INSERT INTO locations
VALUES (
2700,
'Schwanthalerstr. 7031',
'80925',
'Munich',
'Bavaria',
'DE'
);
INSERT INTO locations
VALUES (
2800,
'Rua Frei Caneca 1360 ',
'01307-002',
'Sao Paulo',
'Sao Paulo',
'BR'
);
INSERT INTO locations
VALUES (
2900,
'20 Rue des Corps-Saints',
'1730',
'Geneva',
'Geneve',
'CH'
);
INSERT INTO locations
VALUES (
3000,
'Murtenstrasse 921',
'3095',
'Bern',
'BE',
'CH'
);
INSERT INTO locations
VALUES (
3100,
'Pieter Breughelstraat 837',
'3029SK',
'Utrecht',
'Utrecht',
'NL'
);
INSERT INTO locations
VALUES (
3200,
'Mariano Escobedo 9991',
'11932',
'Mexico City',
'Distrito Federal,',
'MX'
);
COMMIT;
SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO departments
VALUES (
10,
'Administration',
200,
1700
);
INSERT INTO departments
VALUES (
20,
'Marketing',
201,
1800
);
INSERT INTO departments
VALUES (
30,
'Purchasing',
114,
1700
);
INSERT INTO departments
VALUES (
40,
'Human Resources',
203,
2400
);
INSERT INTO departments
VALUES (
50,
'Shipping',
121,
1500
);
INSERT INTO departments
VALUES (
60,
'IT',
103,
1400
);
INSERT INTO departments
VALUES (
70,
'Public Relations',
204,
2700
);
INSERT INTO departments
VALUES (
80,
'Sales',
145,
2500
);
INSERT INTO departments
VALUES (
90,
'Executive',
100,
1700
);
INSERT INTO departments
VALUES (
100,
'Finance',
108,
1700
);
INSERT INTO departments
VALUES (
110,
'Accounting',
205,
1700
);
INSERT INTO departments
VALUES (
120,
'Treasury',
NULL,
1700
);
INSERT INTO departments
VALUES (
130,
'Corporate Tax',
NULL,
1700
);
INSERT INTO departments
VALUES (
140,
'Control And Credit',
NULL,
1700
);
INSERT INTO departments
VALUES (
150,
'Shareholder Services',
NULL,
1700
);
INSERT INTO departments
VALUES (
160,
'Benefits',
NULL,
1700
);
INSERT INTO departments
VALUES (
170,
'Manufacturing',
NULL,
1700
);
INSERT INTO departments
VALUES (
180,
'Construction',
NULL,
1700
);
INSERT INTO departments
VALUES (
190,
'Contracting',
NULL,
1700
);
INSERT INTO departments
VALUES (
200,
'Operations',
NULL,
1700
);
INSERT INTO departments
VALUES (
210,
'IT Support',
NULL,
1700
);
INSERT INTO departments
VALUES (
220,
'NOC',
NULL,
1700
);
INSERT INTO departments
VALUES (
230,
'IT Helpdesk',
NULL,
1700
);
INSERT INTO departments
VALUES (
240,
'Government Sales',
NULL,
1700
);
INSERT INTO departments
VALUES (
250,
'Retail Sales',
NULL,
1700
);
INSERT INTO departments
VALUES (
260,
'Recruiting',
NULL,
1700
);
INSERT INTO departments
VALUES (
270,
'Payroll',
NULL,
1700
);
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
INSERT INTO jobs
VALUES (
'AD_PRES',
'President',
20000,
40000
);
INSERT INTO jobs
VALUES (
'AD_VP',
'Administration Vice President',
15000,
30000
);
INSERT INTO jobs
VALUES (
'AD_ASST',
'Administration Assistant',
3000,
6000
);
INSERT INTO jobs
VALUES (
'FI_MGR',
'Finance Manager',
8200,
16000
);
INSERT INTO jobs
VALUES (
'FI_ACCOUNT',
'Accountant',
4200,
9000
);
INSERT INTO jobs
VALUES (
'AC_MGR',
'Accounting Manager',
8200,
16000
);
INSERT INTO jobs
VALUES (
'AC_ACCOUNT',
'Public Accountant',
4200,
9000
);
INSERT INTO jobs
VALUES (
'SA_MAN',
'Sales Manager',
10000,
20000
);
INSERT INTO jobs
VALUES (
'SA_REP',
'Sales Representative',
6000,
12000
);
INSERT INTO jobs
VALUES (
'PU_MAN',
'Purchasing Manager',
8000,
15000
);
INSERT INTO jobs
VALUES (
'PU_CLERK',
'Purchasing Clerk',
2500,
5500
);
INSERT INTO jobs
VALUES (
'ST_MAN',
'Stock Manager',
5500,
8500
);
INSERT INTO jobs
VALUES (
'ST_CLERK',
'Stock Clerk',
2000,
5000
);
INSERT INTO jobs
VALUES (
'SH_CLERK',
'Shipping Clerk',
2500,
5500
);
INSERT INTO jobs
VALUES (
'IT_PROG',
'Programmer',
4000,
10000
);
INSERT INTO jobs
VALUES (
'MK_MAN',
'Marketing Manager',
9000,
15000
);
INSERT INTO jobs
VALUES (
'MK_REP',
'Marketing Representative',
4000,
9000
);
INSERT INTO jobs
VALUES (
'HR_REP',
'Human Resources Representative',
4000,
9000
);
INSERT INTO jobs
VALUES (
'PR_REP',
'Public Relations Representative',
4500,
10500
);
COMMIT;
INSERT INTO employees
VALUES (
100,
'Steven',
'King',
'SKING',
'515.123.4567',
STR_TO_DATE('17-JUN-1987', '%d-%M-%Y'),
'AD_PRES',
24000,
NULL,
NULL,
90
);
INSERT INTO employees
VALUES (
101,
'Neena',
'Kochhar',
'NKOCHHAR',
'515.123.4568',
STR_TO_DATE('21-SEP-1989', '%d-%M-%Y'),
'AD_VP',
17000,
NULL,
100,
90
);
INSERT INTO employees
VALUES (
102,
'Lex',
'De Haan',
'LDEHAAN',
'515.123.4569',
STR_TO_DATE('13-JAN-1993', '%d-%M-%Y'),
'AD_VP',
17000,
NULL,
100,
90
);
INSERT INTO employees
VALUES (
103,
'Alexander',
'Hunold',
'AHUNOLD',
'590.423.4567',
STR_TO_DATE('03-JAN-1990', '%d-%M-%Y'),
'IT_PROG',
9000,
NULL,
102,
60
);
INSERT INTO employees
VALUES (
104,
'Bruce',
'Ernst',
'BERNST',
'590.423.4568',
STR_TO_DATE('21-MAY-1991', '%d-%M-%Y'),
'IT_PROG',
6000,
NULL,
103,
60
);
INSERT INTO employees
VALUES (
105,
'David',
'Austin',
'DAUSTIN',
'590.423.4569',
STR_TO_DATE('25-JUN-1997', '%d-%M-%Y'),
'IT_PROG',
4800,
NULL,
103,
60
);
INSERT INTO employees
VALUES (
106,
'Valli',
'Pataballa',
'VPATABAL',
'590.423.4560',
STR_TO_DATE('05-FEB-1998', '%d-%M-%Y'),
'IT_PROG',
4800,
NULL,
103,
60
);
INSERT INTO employees
VALUES (
107,
'Diana',
'Lorentz',
'DLORENTZ',
'590.423.5567',
STR_TO_DATE('07-FEB-1999', '%d-%M-%Y'),
'IT_PROG',
4200,
NULL,
103,
60
);
INSERT INTO employees
VALUES (
108,
'Nancy',
'Greenberg',
'NGREENBE',
'515.124.4569',
STR_TO_DATE('17-AUG-1994', '%d-%M-%Y'),
'FI_MGR',
12000,
NULL,
101,
100
);
INSERT INTO employees
VALUES (
109,
'Daniel',
'Faviet',
'DFAVIET',
'515.124.4169',
STR_TO_DATE('16-AUG-1994', '%d-%M-%Y'),
'FI_ACCOUNT',
9000,
NULL,
108,
100
);
INSERT INTO employees
VALUES (
110,
'John',
'Chen',
'JCHEN',
'515.124.4269',
STR_TO_DATE('28-SEP-1997', '%d-%M-%Y'),
'FI_ACCOUNT',
8200,
NULL,
108,
100
);
INSERT INTO employees
VALUES (
111,
'Ismael',
'Sciarra',
'ISCIARRA',
'515.124.4369',
STR_TO_DATE('30-SEP-1997', '%d-%M-%Y'),
'FI_ACCOUNT',
7700,
NULL,
108,
100
);
INSERT INTO employees
VALUES (
112,
'Jose Manuel',
'Urman',
'JMURMAN',
'515.124.4469',
STR_TO_DATE('07-MAR-1998', '%d-%M-%Y'),
'FI_ACCOUNT',
7800,
NULL,
108,
100
);
INSERT INTO employees
VALUES (
113,
'Luis',
'Popp',
'LPOPP',
'515.124.4567',
STR_TO_DATE('07-DEC-1999', '%d-%M-%Y'),
'FI_ACCOUNT',
6900,
NULL,
108,
100
);
INSERT INTO employees
VALUES (
114,
'Den',
'Raphaely',
'DRAPHEAL',
'515.127.4561',
STR_TO_DATE('07-DEC-1994', '%d-%M-%Y'),
'PU_MAN',
11000,
NULL,
100,
30
);
INSERT INTO employees
VALUES (
115,
'Alexander',
'Khoo',
'AKHOO',
'515.127.4562',
STR_TO_DATE('18-MAY-1995', '%d-%M-%Y'),
'PU_CLERK',
3100,
NULL,
114,
30
);
INSERT INTO employees
VALUES (
116,
'Shelli',
'Baida',
'SBAIDA',
'515.127.4563',
STR_TO_DATE('24-DEC-1997', '%d-%M-%Y'),
'PU_CLERK',
2900,
NULL,
114,
30
);
INSERT INTO employees
VALUES (
117,
'Sigal',
'Tobias',
'STOBIAS',
'515.127.4564',
STR_TO_DATE('24-JUL-1997', '%d-%M-%Y'),
'PU_CLERK',
2800,
NULL,
114,
30
);
INSERT INTO employees
VALUES (
118,
'Guy',
'Himuro',
'GHIMURO',
'515.127.4565',
STR_TO_DATE('15-NOV-1998', '%d-%M-%Y'),
'PU_CLERK',
2600,
NULL,
114,
30
);
INSERT INTO employees
VALUES (
119,
'Karen',
'Colmenares',
'KCOLMENA',
'515.127.4566',
STR_TO_DATE('10-AUG-1999', '%d-%M-%Y'),
'PU_CLERK',
2500,
NULL,
114,
30
);
INSERT INTO employees
VALUES (
120,
'Matthew',
'Weiss',
'MWEISS',
'650.123.1234',
STR_TO_DATE('18-JUL-1996', '%d-%M-%Y'),
'ST_MAN',
8000,
NULL,
100,
50
);
INSERT INTO employees
VALUES (
121,
'Adam',
'Fripp',
'AFRIPP',
'650.123.2234',
STR_TO_DATE('10-APR-1997', '%d-%M-%Y'),
'ST_MAN',
8200,
NULL,
100,
50
);
INSERT INTO employees
VALUES (
122,
'Payam',
'Kaufling',
'PKAUFLIN',
'650.123.3234',
STR_TO_DATE('01-MAY-1995', '%d-%M-%Y'),
'ST_MAN',
7900,
NULL,
100,
50
);
INSERT INTO employees
VALUES (
123,
'Shanta',
'Vollman',
'SVOLLMAN',
'650.123.4234',
STR_TO_DATE('10-OCT-1997', '%d-%M-%Y'),
'ST_MAN',
6500,
NULL,
100,
50
);
INSERT INTO employees
VALUES (
124,
'Kevin',
'Mourgos',
'KMOURGOS',
'650.123.5234',
STR_TO_DATE('16-NOV-1999', '%d-%M-%Y'),
'ST_MAN',
5800,
NULL,
100,
50
);
INSERT INTO employees
VALUES (
125,
'Julia',
'Nayer',
'JNAYER',
'650.124.1214',
STR_TO_DATE('16-JUL-1997', '%d-%M-%Y'),
'ST_CLERK',
3200,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
126,
'Irene',
'Mikkilineni',
'IMIKKILI',
'650.124.1224',
STR_TO_DATE('28-SEP-1998', '%d-%M-%Y'),
'ST_CLERK',
2700,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
127,
'James',
'Landry',
'JLANDRY',
'650.124.1334',
STR_TO_DATE('14-JAN-1999', '%d-%M-%Y'),
'ST_CLERK',
2400,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
128,
'Steven',
'Markle',
'SMARKLE',
'650.124.1434',
STR_TO_DATE('08-MAR-2000', '%d-%M-%Y'),
'ST_CLERK',
2200,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
129,
'Laura',
'Bissot',
'LBISSOT',
'650.124.5234',
STR_TO_DATE('20-AUG-1997', '%d-%M-%Y'),
'ST_CLERK',
3300,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
130,
'Mozhe',
'Atkinson',
'MATKINSO',
'650.124.6234',
STR_TO_DATE('30-OCT-1997', '%d-%M-%Y'),
'ST_CLERK',
2800,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
131,
'James',
'Marlow',
'JAMRLOW',
'650.124.7234',
STR_TO_DATE('16-FEB-1997', '%d-%M-%Y'),
'ST_CLERK',
2500,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
132,
'TJ',
'Olson',
'TJOLSON',
'650.124.8234',
STR_TO_DATE('10-APR-1999', '%d-%M-%Y'),
'ST_CLERK',
2100,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
133,
'Jason',
'Mallin',
'JMALLIN',
'650.127.1934',
STR_TO_DATE('14-JUN-1996', '%d-%M-%Y'),
'ST_CLERK',
3300,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
134,
'Michael',
'Rogers',
'MROGERS',
'650.127.1834',
STR_TO_DATE('26-AUG-1998', '%d-%M-%Y'),
'ST_CLERK',
2900,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
135,
'Ki',
'Gee',
'KGEE',
'650.127.1734',
STR_TO_DATE('12-DEC-1999', '%d-%M-%Y'),
'ST_CLERK',
2400,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
136,
'Hazel',
'Philtanker',
'HPHILTAN',
'650.127.1634',
STR_TO_DATE('06-FEB-2000', '%d-%M-%Y'),
'ST_CLERK',
2200,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
137,
'Renske',
'Ladwig',
'RLADWIG',
'650.121.1234',
STR_TO_DATE('14-JUL-1995', '%d-%M-%Y'),
'ST_CLERK',
3600,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
138,
'Stephen',
'Stiles',
'SSTILES',
'650.121.2034',
STR_TO_DATE('26-OCT-1997', '%d-%M-%Y'),
'ST_CLERK',
3200,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
139,
'John',
'Seo',
'JSEO',
'650.121.2019',
STR_TO_DATE('12-FEB-1998', '%d-%M-%Y'),
'ST_CLERK',
2700,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
140,
'Joshua',
'Patel',
'JPATEL',
'650.121.1834',
STR_TO_DATE('06-APR-1998', '%d-%M-%Y'),
'ST_CLERK',
2500,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
141,
'Trenna',
'Rajs',
'TRAJS',
'650.121.8009',
STR_TO_DATE('17-OCT-1995', '%d-%M-%Y'),
'ST_CLERK',
3500,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
142,
'Curtis',
'Davies',
'CDAVIES',
'650.121.2994',
STR_TO_DATE('29-JAN-1997', '%d-%M-%Y'),
'ST_CLERK',
3100,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
143,
'Randall',
'Matos',
'RMATOS',
'650.121.2874',
STR_TO_DATE('15-MAR-1998', '%d-%M-%Y'),
'ST_CLERK',
2600,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
144,
'Peter',
'Vargas',
'PVARGAS',
'650.121.2004',
STR_TO_DATE('09-JUL-1998', '%d-%M-%Y'),
'ST_CLERK',
2500,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
145,
'John',
'Russell',
'JRUSSEL',
'011.44.1344.429268',
STR_TO_DATE('01-OCT-1996', '%d-%M-%Y'),
'SA_MAN',
14000,
.4,
100,
80
);
INSERT INTO employees
VALUES (
146,
'Karen',
'Partners',
'KPARTNER',
'011.44.1344.467268',
STR_TO_DATE('05-JAN-1997', '%d-%M-%Y'),
'SA_MAN',
13500,
.3,
100,
80
);
INSERT INTO employees
VALUES (
147,
'Alberto',
'Errazuriz',
'AERRAZUR',
'011.44.1344.429278',
STR_TO_DATE('10-MAR-1997', '%d-%M-%Y'),
'SA_MAN',
12000,
.3,
100,
80
);
INSERT INTO employees
VALUES (
148,
'Gerald',
'Cambrault',
'GCAMBRAU',
'011.44.1344.619268',
STR_TO_DATE('15-OCT-1999', '%d-%M-%Y'),
'SA_MAN',
11000,
.3,
100,
80
);
INSERT INTO employees
VALUES (
149,
'Eleni',
'Zlotkey',
'EZLOTKEY',
'011.44.1344.429018',
STR_TO_DATE('29-JAN-2000', '%d-%M-%Y'),
'SA_MAN',
10500,
.2,
100,
80
);
INSERT INTO employees
VALUES (
150,
'Peter',
'Tucker',
'PTUCKER',
'011.44.1344.129268',
STR_TO_DATE('30-JAN-1997', '%d-%M-%Y'),
'SA_REP',
10000,
.3,
145,
80
);
INSERT INTO employees
VALUES (
151,
'David',
'Bernstein',
'DBERNSTE',
'011.44.1344.345268',
STR_TO_DATE('24-MAR-1997', '%d-%M-%Y'),
'SA_REP',
9500,
.25,
145,
80
);
INSERT INTO employees
VALUES (
152,
'Peter',
'Hall',
'PHALL',
'011.44.1344.478968',
STR_TO_DATE('20-AUG-1997', '%d-%M-%Y'),
'SA_REP',
9000,
.25,
145,
80
);
INSERT INTO employees
VALUES (
153,
'Christopher',
'Olsen',
'COLSEN',
'011.44.1344.498718',
STR_TO_DATE('30-MAR-1998', '%d-%M-%Y'),
'SA_REP',
8000,
.2,
145,
80
);
INSERT INTO employees
VALUES (
154,
'Nanette',
'Cambrault',
'NCAMBRAU',
'011.44.1344.987668',
STR_TO_DATE('09-DEC-1998', '%d-%M-%Y'),
'SA_REP',
7500,
.2,
145,
80
);
INSERT INTO employees
VALUES (
155,
'Oliver',
'Tuvault',
'OTUVAULT',
'011.44.1344.486508',
STR_TO_DATE('23-NOV-1999', '%d-%M-%Y'),
'SA_REP',
7000,
.15,
145,
80
);
INSERT INTO employees
VALUES (
156,
'Janette',
'King',
'JKING',
'011.44.1345.429268',
STR_TO_DATE('30-JAN-1996', '%d-%M-%Y'),
'SA_REP',
10000,
.35,
146,
80
);
INSERT INTO employees
VALUES (
157,
'Patrick',
'Sully',
'PSULLY',
'011.44.1345.929268',
STR_TO_DATE('04-MAR-1996', '%d-%M-%Y'),
'SA_REP',
9500,
.35,
146,
80
);
INSERT INTO employees
VALUES (
158,
'Allan',
'McEwen',
'AMCEWEN',
'011.44.1345.829268',
STR_TO_DATE('01-AUG-1996', '%d-%M-%Y'),
'SA_REP',
9000,
.35,
146,
80
);
INSERT INTO employees
VALUES (
159,
'Lindsey',
'Smith',
'LSMITH',
'011.44.1345.729268',
STR_TO_DATE('10-MAR-1997', '%d-%M-%Y'),
'SA_REP',
8000,
.3,
146,
80
);
INSERT INTO employees
VALUES (
160,
'Louise',
'Doran',
'LDORAN',
'011.44.1345.629268',
STR_TO_DATE('15-DEC-1997', '%d-%M-%Y'),
'SA_REP',
7500,
.3,
146,
80
);
INSERT INTO employees
VALUES (
161,
'Sarath',
'Sewall',
'SSEWALL',
'011.44.1345.529268',
STR_TO_DATE('03-NOV-1998', '%d-%M-%Y'),
'SA_REP',
7000,
.25,
146,
80
);
INSERT INTO employees
VALUES (
162,
'Clara',
'Vishney',
'CVISHNEY',
'011.44.1346.129268',
STR_TO_DATE('11-NOV-1997', '%d-%M-%Y'),
'SA_REP',
10500,
.25,
147,
80
);
INSERT INTO employees
VALUES (
163,
'Danielle',
'Greene',
'DGREENE',
'011.44.1346.229268',
STR_TO_DATE('19-MAR-1999', '%d-%M-%Y'),
'SA_REP',
9500,
.15,
147,
80
);
INSERT INTO employees
VALUES (
164,
'Mattea',
'Marvins',
'MMARVINS',
'011.44.1346.329268',
STR_TO_DATE('24-JAN-2000', '%d-%M-%Y'),
'SA_REP',
7200,
.10,
147,
80
);
INSERT INTO employees
VALUES (
165,
'David',
'Lee',
'DLEE',
'011.44.1346.529268',
STR_TO_DATE('23-FEB-2000', '%d-%M-%Y'),
'SA_REP',
6800,
.1,
147,
80
);
INSERT INTO employees
VALUES (
166,
'Sundar',
'Ande',
'SANDE',
'011.44.1346.629268',
STR_TO_DATE('24-MAR-2000', '%d-%M-%Y'),
'SA_REP',
6400,
.10,
147,
80
);
INSERT INTO employees
VALUES (
167,
'Amit',
'Banda',
'ABANDA',
'011.44.1346.729268',
STR_TO_DATE('21-APR-2000', '%d-%M-%Y'),
'SA_REP',
6200,
.10,
147,
80
);
INSERT INTO employees
VALUES (
168,
'Lisa',
'Ozer',
'LOZER',
'011.44.1343.929268',
STR_TO_DATE('11-MAR-1997', '%d-%M-%Y'),
'SA_REP',
11500,
.25,
148,
80
);
INSERT INTO employees
VALUES (
169,
'Harrison',
'Bloom',
'HBLOOM',
'011.44.1343.829268',
STR_TO_DATE('23-MAR-1998', '%d-%M-%Y'),
'SA_REP',
10000,
.20,
148,
80
);
INSERT INTO employees
VALUES (
170,
'Tayler',
'Fox',
'TFOX',
'011.44.1343.729268',
STR_TO_DATE('24-JAN-1998', '%d-%M-%Y'),
'SA_REP',
9600,
.20,
148,
80
);
INSERT INTO employees
VALUES (
171,
'William',
'Smith',
'WSMITH',
'011.44.1343.629268',
STR_TO_DATE('23-FEB-1999', '%d-%M-%Y'),
'SA_REP',
7400,
.15,
148,
80
);
INSERT INTO employees
VALUES (
172,
'Elizabeth',
'Bates',
'EBATES',
'011.44.1343.529268',
STR_TO_DATE('24-MAR-1999', '%d-%M-%Y'),
'SA_REP',
7300,
.15,
148,
80
);
INSERT INTO employees
VALUES (
173,
'Sundita',
'Kumar',
'SKUMAR',
'011.44.1343.329268',
STR_TO_DATE('21-APR-2000', '%d-%M-%Y'),
'SA_REP',
6100,
.10,
148,
80
);
INSERT INTO employees
VALUES (
174,
'Ellen',
'Abel',
'EABEL',
'011.44.1644.429267',
STR_TO_DATE('11-MAY-1996', '%d-%M-%Y'),
'SA_REP',
11000,
.30,
149,
80
);
INSERT INTO employees
VALUES (
175,
'Alyssa',
'Hutton',
'AHUTTON',
'011.44.1644.429266',
STR_TO_DATE('19-MAR-1997', '%d-%M-%Y'),
'SA_REP',
8800,
.25,
149,
80
);
INSERT INTO employees
VALUES (
176,
'Jonathon',
'Taylor',
'JTAYLOR',
'011.44.1644.429265',
STR_TO_DATE('24-MAR-1998', '%d-%M-%Y'),
'SA_REP',
8600,
.20,
149,
80
);
INSERT INTO employees
VALUES (
177,
'Jack',
'Livingston',
'JLIVINGS',
'011.44.1644.429264',
STR_TO_DATE('23-APR-1998', '%d-%M-%Y'),
'SA_REP',
8400,
.20,
149,
80
);
INSERT INTO employees
VALUES (
178,
'Kimberely',
'Grant',
'KGRANT',
'011.44.1644.429263',
STR_TO_DATE('24-MAY-1999', '%d-%M-%Y'),
'SA_REP',
7000,
.15,
149,
NULL
);
INSERT INTO employees
VALUES (
179,
'Charles',
'Johnson',
'CJOHNSON',
'011.44.1644.429262',
STR_TO_DATE('04-JAN-2000', '%d-%M-%Y'),
'SA_REP',
6200,
.10,
149,
80
);
INSERT INTO employees
VALUES (
180,
'Winston',
'Taylor',
'WTAYLOR',
'650.507.9876',
STR_TO_DATE('24-JAN-1998', '%d-%M-%Y'),
'SH_CLERK',
3200,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
181,
'Jean',
'Fleaur',
'JFLEAUR',
'650.507.9877',
STR_TO_DATE('23-FEB-1998', '%d-%M-%Y'),
'SH_CLERK',
3100,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
182,
'Martha',
'Sullivan',
'MSULLIVA',
'650.507.9878',
STR_TO_DATE('21-JUN-1999', '%d-%M-%Y'),
'SH_CLERK',
2500,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
183,
'Girard',
'Geoni',
'GGEONI',
'650.507.9879',
STR_TO_DATE('03-FEB-2000', '%d-%M-%Y'),
'SH_CLERK',
2800,
NULL,
120,
50
);
INSERT INTO employees
VALUES (
184,
'Nandita',
'Sarchand',
'NSARCHAN',
'650.509.1876',
STR_TO_DATE('27-JAN-1996', '%d-%M-%Y'),
'SH_CLERK',
4200,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
185,
'Alexis',
'Bull',
'ABULL',
'650.509.2876',
STR_TO_DATE('20-FEB-1997', '%d-%M-%Y'),
'SH_CLERK',
4100,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
186,
'Julia',
'Dellinger',
'JDELLING',
'650.509.3876',
STR_TO_DATE('24-JUN-1998', '%d-%M-%Y'),
'SH_CLERK',
3400,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
187,
'Anthony',
'Cabrio',
'ACABRIO',
'650.509.4876',
STR_TO_DATE('07-FEB-1999', '%d-%M-%Y'),
'SH_CLERK',
3000,
NULL,
121,
50
);
INSERT INTO employees
VALUES (
188,
'Kelly',
'Chung',
'KCHUNG',
'650.505.1876',
STR_TO_DATE('14-JUN-1997', '%d-%M-%Y'),
'SH_CLERK',
3800,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
189,
'Jennifer',
'Dilly',
'JDILLY',
'650.505.2876',
STR_TO_DATE('13-AUG-1997', '%d-%M-%Y'),
'SH_CLERK',
3600,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
190,
'Timothy',
'Gates',
'TGATES',
'650.505.3876',
STR_TO_DATE('11-JUL-1998', '%d-%M-%Y'),
'SH_CLERK',
2900,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
191,
'Randall',
'Perkins',
'RPERKINS',
'650.505.4876',
STR_TO_DATE('19-DEC-1999', '%d-%M-%Y'),
'SH_CLERK',
2500,
NULL,
122,
50
);
INSERT INTO employees
VALUES (
192,
'Sarah',
'Bell',
'SBELL',
'650.501.1876',
STR_TO_DATE('04-FEB-1996', '%d-%M-%Y'),
'SH_CLERK',
4000,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
193,
'Britney',
'Everett',
'BEVERETT',
'650.501.2876',
STR_TO_DATE('03-MAR-1997', '%d-%M-%Y'),
'SH_CLERK',
3900,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
194,
'Samuel',
'McCain',
'SMCCAIN',
'650.501.3876',
STR_TO_DATE('01-JUL-1998', '%d-%M-%Y'),
'SH_CLERK',
3200,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
195,
'Vance',
'Jones',
'VJONES',
'650.501.4876',
STR_TO_DATE('17-MAR-1999', '%d-%M-%Y'),
'SH_CLERK',
2800,
NULL,
123,
50
);
INSERT INTO employees
VALUES (
196,
'Alana',
'Walsh',
'AWALSH',
'650.507.9811',
STR_TO_DATE('24-APR-1998', '%d-%M-%Y'),
'SH_CLERK',
3100,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
197,
'Kevin',
'Feeney',
'KFEENEY',
'650.507.9822',
STR_TO_DATE('23-MAY-1998', '%d-%M-%Y'),
'SH_CLERK',
3000,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
198,
'Donald',
'OConnell',
'DOCONNEL',
'650.507.9833',
STR_TO_DATE('21-JUN-1999', '%d-%M-%Y'),
'SH_CLERK',
2600,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
199,
'Douglas',
'Grant',
'DGRANT',
'650.507.9844',
STR_TO_DATE('13-JAN-2000', '%d-%M-%Y'),
'SH_CLERK',
2600,
NULL,
124,
50
);
INSERT INTO employees
VALUES (
200,
'Jennifer',
'Whalen',
'JWHALEN',
'515.123.4444',
STR_TO_DATE('17-SEP-1987', '%d-%M-%Y'),
'AD_ASST',
4400,
NULL,
101,
10
);
INSERT INTO employees
VALUES (
201,
'Michael',
'Hartstein',
'MHARTSTE',
'515.123.5555',
STR_TO_DATE('17-FEB-1996', '%d-%M-%Y'),
'MK_MAN',
13000,
NULL,
100,
20
);
INSERT INTO employees
VALUES (
202,
'Pat',
'Fay',
'PFAY',
'603.123.6666',
STR_TO_DATE('17-AUG-1997', '%d-%M-%Y'),
'MK_REP',
6000,
NULL,
201,
20
);
INSERT INTO employees
VALUES (
203,
'Susan',
'Mavris',
'SMAVRIS',
'515.123.7777',
STR_TO_DATE('07-JUN-1994', '%d-%M-%Y'),
'HR_REP',
6500,
NULL,
101,
40
);
INSERT INTO employees
VALUES (
204,
'Hermann',
'Baer',
'HBAER',
'515.123.8888',
STR_TO_DATE('07-JUN-1994', '%d-%M-%Y'),
'PR_REP',
10000,
NULL,
101,
70
);
INSERT INTO employees
VALUES (
205,
'Shelley',
'Higgins',
'SHIGGINS',
'515.123.8080',
STR_TO_DATE('07-JUN-1994', '%d-%M-%Y'),
'AC_MGR',
12000,
NULL,
101,
110
);
INSERT INTO employees
VALUES (
206,
'William',
'Gietz',
'WGIETZ',
'51hr5.123.8181',
STR_TO_DATE('07-JUN-1994', '%d-%M-%Y'),
'AC_ACCOUNT',
8300,
NULL,
205,
110
);
COMMIT;
INSERT INTO job_history
VALUES (
102,
STR_TO_DATE('13-Jan-1993', '%d-%M-%Y'),
STR_TO_DATE('24-Jul-1998', '%d-%M-%Y'),
'IT_PROG',
60
);
INSERT INTO job_history
VALUES (
101,
STR_TO_DATE('21-Sep-1989', '%d-%M-%Y'),
STR_TO_DATE('27-Oct-1993', '%d-%M-%Y'),
'AC_ACCOUNT',
110
);
INSERT INTO job_history
VALUES (
101,
STR_TO_DATE('28-Oct-1993','%d-%M-%Y'),
STR_TO_DATE('15-Mar-1997','%d-%M-%Y'),
'AC_MGR',
110
);
INSERT INTO job_history
VALUES (
201,
STR_TO_DATE('27-Feb-1996','%d-%M-%Y'),
STR_TO_DATE('19-Dec-1999','%d-%M-%Y'),
'MK_REP',
20
);
INSERT INTO job_history
VALUES (
114,
STR_TO_DATE('24-Mar-1998','%d-%M-%Y'),
STR_TO_DATE('31-Dec-1999','%d-%M-%Y'),
'ST_CLERK',
50
);
INSERT INTO job_history
VALUES (
122,
STR_TO_DATE('01-Jan-1999','%d-%M-%Y'),
STR_TO_DATE('31-Dec-1999','%d-%M-%Y'),
'ST_CLERK',
50
);
INSERT INTO job_history
VALUES (
200,
STR_TO_DATE('17-Sep-1987','%d-%M-%Y'),
STR_TO_DATE('17-Jun-1993','%d-%M-%Y'),
'AD_ASST',
90
);
INSERT INTO job_history
VALUES (
176,
STR_TO_DATE('24-Mar-1998','%d-%M-%Y'),
STR_TO_DATE('31-Dec-1998','%d-%M-%Y'),
'SA_REP',
80
);
INSERT INTO job_history
VALUES (
176,
STR_TO_DATE('01-Jan-1999','%d-%M-%Y'),
STR_TO_DATE('31-Dec-1999','%d-%M-%Y'),
'SA_MAN',
80
);
INSERT INTO job_history
VALUES (
200,
STR_TO_DATE('01-Jul-1994','%d-%M-%Y'),
STR_TO_DATE('31-Dec-1998','%d-%M-%Y'),
'AC_ACCOUNT',
90
);
COMMIT;✔️ now last thing is the FOREIGN KEYS:
After you run all the insertion data we need to save and rename this script like this:
again right-click on your database and choose SQL Editor / new SQL Script :
copy and past these codes:
ALTER TABLE countries ADD FOREIGN KEY (region_id) REFERENCES regions(region_id);
ALTER TABLE locations ADD FOREIGN KEY (country_id) REFERENCES countries(country_id);
ALTER TABLE departments ADD FOREIGN KEY (location_id) REFERENCES locations(location_id);
ALTER TABLE employees ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id);
ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);
ALTER TABLE employees ADD FOREIGN KEY (manager_id) REFERENCES employees(employee_id);
ALTER TABLE departments ADD FOREIGN KEY (manager_id) REFERENCES employees (employee_id);
ALTER TABLE job_history ADD FOREIGN KEY (employee_id) REFERENCES employees(employee_id);
ALTER TABLE job_history ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id);
ALTER TABLE job_history ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);you can save and rename this script too!
and run all of them you gonne see 10 qurey done!
again right-click on your database and choose SQL Editor / new SQL Script :
NOW is time to Practice run any qurey you want to learn more !!!! example:
select first_name , last_name from employees ;
more Example :
//display firstname and lastname from employeeid 110
select * from employees
where employee_id = 110;
//display job_id and job title from jobs table
select job_id,job_title from jobs;
//display all different names from employees
select distinct FIRST_NAME from EMPLOYEES;
select * from EMPLOYEES
where FIRST_NAME='David';// '' it is case sensitive
//display firstname,lastname,salary where firstname is Peter
select first_name,last_name,salary from employees
where first_name='Peter';
//display email of who is making less than 6000
select first_name,email from EMPLOYEES
where SALARY<6000;
//display all info from employees where employee_id between 100 and 120
select * from EMPLOYEES
where EMPLOYEE_ID between 100 and 120; // range is included
//display all info from employees who is working as IT_PROG or MK_MAN
//solution 1
select * from EMPLOYEES
where JOB_ID='IT_PROG' or JOB_ID='MK_MAN' or JOB_ID='SA_REP';
select * from EMPLOYEES
order by SALARY asc;
//display all employees where first_name startswith B and lenght of it 5 letter
select * from EMPLOYEES
where first_name like 'B____';
/* display all employees where first_name startswith B */
select * from EMPLOYEES
where first_name like 'B%';
/* display 5 letter first names from employees where middle char is z */
select * from employees
WHERE FIRST_NAME like '__z__';
//display firstnames where second letter is a
select first_name from EMPLOYEES
where FIRST_NAME like '_a%';
//Task 4:display all Countries where Region_ID is 1 and Country_Name is not Belgium
select * from COUNTRIES
where REGION_ID=1 and COUNTRY_NAME!='Belgium';
//How many departments we have ?
select * from departments;
select count(*) from departments;
//how many employees working as IT_PROG or SA_REP
select count(*) from EMPLOYEES
where JOB_ID in ('IT_PROG','SA_REP');
select round(avg(salary),3) from EMPLOYEES; // 6462.364
//get me total salary for EACH department from Employees table
select DEPARTMENT_ID,sum(salary),count(*),max(salary),min(salary),round(avg(salary)) from EMPLOYEES
where DEPARTMENT_ID is not null -- to remove null department id from result
group by DEPARTMENT_ID;
//order results based on max salary in asc
select DEPARTMENT_ID,sum(salary),count(*),max(salary),min(salary),round(avg(salary)) from EMPLOYEES
where DEPARTMENT_ID is not null -- to remove null department id from result
group by DEPARTMENT_ID
order by max(salary);
//display department id where employees count is bigger than 5
select DEPARTMENT_ID,count(*) from EMPLOYEES
group by DEPARTMENT_ID
having count(*)>5 ;
//IQ --> display duplicate(more than one) firstnames from employees table
select first_name,count(*) from EMPLOYEES
group by first_name
having count(*)>1
order by 2 desc;
//display annual salary for all employees as annual_salary
select FIRST_NAME, SALARY*12 from EMPLOYEES;
//find second highest dynamicly
select max(salary) from EMPLOYEES
where salary < (select max(salary) from EMPLOYEES);
//how many employee we have for each department name
select DEPARTMENT_NAME,count(*) from departments d left join employees e
on d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by DEPARTMENT_NAME;
//LEFT JOIN
select FIRST_NAME,DEPARTMENT_NAME from departments d left join employees e
on d.DEPARTMENT_ID = e.DEPARTMENT_ID;
// Display All cities and related country names including with countries without city
select * from COUNTRIES;
select distinct COUNTRY_ID from LOCATIONS;
// RIGHT JOIN
select CITY,COUNTRY_NAME from LOCATIONS L right join COUNTRIES C
on L.COUNTRY_ID = C.COUNTRY_ID;
// Display manager name of 'Neena'
select MANAGER_ID from employees
where FIRST_NAME='Neena';
select FIRST_NAME,LAST_NAME from EMPLOYEES
where EMPLOYEE_ID=(select MANAGER_ID from employees
where FIRST_NAME='Neena');
✔️ you can practice with IntelliJ too :
Create new Project , Java - Maven
click on database icon on right side and click on + create new database choose mySQL.
✔️ click download.
fill out the information like this:
click Test connection and you should see this :
✔️ Apply and Okey.
then on right top side of page choose your database click on schema in this case name of mydatabase is "mydatabase"
then type your query : and run it.
✔️ First need to install the extention "Mysql"
Click on "+" under Mysql
type the host name "localhost" and Enter⌨️
Type the user name ➡️ "root" Enter⌨️
For password just Enter without type anything becasue there is no password for this localHost and for Port type "3306" Enter ⌨️
Next step just push Enter on keybord⌨️
✔️ Now you have to see your connection on left side, just like this :
✔️next click under connection , Right-click on your database name and choose "New Query" :
Save it ,give it a name like P1 and Right-click on screen and choose "Run Query" and see the result.
Good luck guys enjoy!