CREATE DATABASE JOINS

DEFAULT CHARACTER SET = 'utf8mb4';

use JOINS;

CREATE TABLE students (

id INT PRIMARY KEY,

name VARCHAR(50)

);

CREATE TABLE scores (

student_id INT,

score INT,

FOREIGN KEY (student_id) REFERENCES students(id)

);

INSERT INTO students (id, name) VALUES

(1, 'Alice'),

(2, 'Bob'),

(3, 'Charlie'),

(4, 'David');

INSERT INTO scores (student_id, score) VALUES

(1, 85),

(3, 92),

(4, 78);

SELECT * from scores;

SELECT columnName

FROM tableA AS t1

INNER JOIN tableB AS t2

ON t1.commonColumnName = t2.commonColumnName;

SELECT tableOne.id,

tableOne.name,

tableTwo.score

FROM students AS tableOne

INNER JOIN scores AS tableTwo

ON tableOne.id = tableTwo.student_id;

use Learning;

SELECT * from employees;

CREATE TABLE departments (

department_id INT PRIMARY KEY AUTO_INCREMENT,

department_name VARCHAR(50) NOT NULL UNIQUE,

location VARCHAR(50),

manager_name VARCHAR(100),

budget DECIMAL(12,2),

established_year YEAR

);

INSERT INTO departments (department_name, location, manager_name, budget, established_year) VALUES

('HR', 'Germany', 'Laura Becker', 500000.00, 2010),

('Sales', 'USA', 'John Smith', 1200000.00, 2008),

('Finance', 'India', 'Priya Sharma', 900000.00, 2012),

('Marketing', 'Bangladesh', 'Rafiq Ahmed', 750000.00, 2015),

('Engineering', 'Canada', 'Alice Johnson', 2000000.00, 2005);

SELECT * FROM departments;

use Learning;

SELECT * from employees;

CREATE TABLE departments (

department_id INT PRIMARY KEY AUTO_INCREMENT,

department_name VARCHAR(50) NOT NULL UNIQUE,

location VARCHAR(50),

manager_name VARCHAR(100),

budget DECIMAL(12,2),

established_year YEAR

);

INSERT INTO departments (department_name, location, manager_name, budget, established_year) VALUES

('HR', 'Germany', 'Laura Becker', 500000.00, 2010),

('Sales', 'USA', 'John Smith', 1200000.00, 2008),

('Finance', 'India', 'Priya Sharma', 900000.00, 2012),

('Marketing', 'Bangladesh', 'Rafiq Ahmed', 750000.00, 2015),

('Engineering', 'Canada', 'Alice Johnson', 2000000.00, 2005);

use Learning;

SELECT * FROM departments;

SELECT * FROM employees;

ALTER TABLE employees

ADD COLUMN department_id INT;

ALTER TABLE employees

FOREIGN KEY (department_id) REFERENCES departments(department_id);

ALTER TABLE employees

ADD COLUMN department_id INT;

ALTER TABLE employees

ADD CONSTRAINT fk_department

FOREIGN KEY (department_id) REFERENCES departments(department_id);