DATA 210 Week 3 Class 1 Resources

#bsit

Activity 1

Create the table as below (start with table headers and then values) :

StudentID	FirstName	LastName	Gender	Age	City	State	Country
1	John	Doe	M	25	New York	NY	USA
2	Jane	Doe	F	22	Los Angeles	CA	USA
3	Bob	Smith	M	27	Chicago	IL	USA
4	Alice	Johnson	F	21	Houston	TX	USA
5	Tom	Jones	M	24	London	NULL	UK
6	Linda	Smith	F	23	Paris	NULL	France

Need help? Follow the code as below to create a table:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Gender CHAR(1),
  Age INT,
  City VARCHAR(50),
  State VARCHAR(50),
  Country VARCHAR(50)
);

Now, insert values in the table with this:

INSERT INTO Students VALUES
  (1, 'John', 'Doe', 'M', 25, 'New York', 'NY', 'USA'),
  (2, 'Jane', 'Doe', 'F', 22, 'Los Angeles', 'CA', 'USA'),
  (3, 'Bob', 'Smith', 'M', 27, 'Chicago', 'IL', 'USA'),
  (4, 'Alice', 'Johnson', 'F', 21, 'Houston', 'TX', 'USA'),
  (5, 'Tom', 'Jones', 'M', 24, 'London', NULL, 'UK'),
  (6, 'Linda', 'Smith', 'F', 23, 'Paris', NULL, 'France');

Now, answer the following questions:

Select all students from the USA.
Select all students from the USA who are over 25 years old.
Select all students who are either from New York or Los Angeles.
Select all students who are not from the USA.
Select all students who are not from the USA and are over 25 years old.
Select all students from the USA.
Select all students from the USA who are over 25 years old.
Select all students who are either from New York or Los Angeles.
Select all students who are not from the USA.
Select all students who are not from the USA and are over 25 years old.
Select all students and include a computed field called 'FullName' which is the concatenation of first and last name.
Select all students and include a computed field called 'GenderAndAge' which is a concatenation of gender and age, separated by a dash.

Solutions

-- 1. Select all students from the USA.
SELECT * FROM Students WHERE Country = 'USA';

-- 2. Select all students from the USA who are over 25 years old.
SELECT * FROM Students WHERE Country = 'USA' AND Age > 25;

-- 3. Select all students who are either from New York or Los Angeles.
SELECT * FROM Students WHERE City = 'New York' OR City = 'Los Angeles';

-- 4. Select all students who are not from the USA.
SELECT * FROM Students WHERE Country <> 'USA';

-- 5. Select all students who are not from the USA and are over 25 years old.
SELECT * FROM Students WHERE Country <> 'USA' AND Age > 25;

-- 6. Select all students whose age is between 22 and 25 inclusive.
SELECT * FROM Students WHERE Age BETWEEN 22 AND 25;

-- 7. Select all students whose last name starts with 'S'.
SELECT * FROM Students WHERE LastName LIKE 'S%';

-- 8. Select all students whose first name is 'Bob' or 'Tom' or 'Linda'.
SELECT * FROM Students WHERE FirstName IN ('Bob', 'Tom', 'Linda');

-- 9. Select all students and include a computed field called 'FullName' which is the concatenation of first and last name.
SELECT *, CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

-- 10. Select all students and include a computed field called 'GenderAndAge' which is a concatenation of gender and age, separated by a dash.
SELECT *, CONCAT(Gender, '-', Age) AS GenderAndAge FROM Students;

Activity 2
Create a table called Employees with columns for employee ID, name, job title, department ID, and salary.

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(50),
  JobTitle VARCHAR(50),
  DepartmentID INT,
  Salary INT
);

Insert some sample data into the Employees table.

INSERT INTO Employees VALUES
  (1, 'John Smith', 'Manager', 1, 80000),
  (2, 'Jane Doe', 'Sales Representative', 2, 60000),
  (3, 'Bob Johnson', 'Software Developer', 3, 70000),
  (4, 'Alice Brown', 'Sales Manager', 2, 90000),
  (5, 'Tom Jones', 'Software Architect', 3, 100000),
  (6, 'Linda Davis', 'HR Specialist', 4, 50000);

Now, work to answer the following questions:

Select all employees from department 3.
Select all employees who make more than $70,000 per year.
Select all employees whose job title contains the word 'Manager'.
Select all employees who work in departments other than department 2.
Select all employees who work in departments other than department 2 and make more than $70,000 per year.
Select all employees whose salary is between $60,000 and $80,000 inclusive.
Select all employees whose name starts with 'J'.
Select all employees whose job title is 'Manager' or 'Sales Representative'.
Select all employees and include a computed field called 'FullName' which is the concatenation of first and last name.
Select all employees and include a computed field called 'DepartmentAndJobTitle' which is a concatenation of department and job title, separated by a comma.

Activity 3

Create table as below:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(50),
  Phone VARCHAR(20),
  Address VARCHAR(100),
  HireDate DATE,
  Salary INT,
  Department VARCHAR(50)
);

Insert the following values:

INSERT INTO Employees VALUES
  (1, 'John', 'Doe', '[email protected]', '555-1234', '123 Main St, Anytown USA', '2020-01-01', 50000, 'Sales'),
  (2, 'Jane', 'Smith', '[email protected]', '555-5678', '456 Oak St, Anytown USA', '2019-05-15', 60000, 'Marketing'),
  (3, 'Bob', 'Johnson', '[email protected]', '555-4321', '789 Pine St, Anytown USA', '2018-09-01', 70000, 'Finance'),
  (4, 'Alice', 'Williams', '[email protected]', '555-8765', '321 Maple St, Anytown USA', '2021-03-01', 55000, 'Sales'),
  (5, 'Tom', 'Jones', '[email protected]', '555-2468', '654 Elm St, Anytown USA', '2017-01-01', 75000, 'Finance'),
  (6, 'Linda', 'Davis', '[email protected]', '555-3698', '987 Cedar St, Anytown USA', '2022-01-01', 80000, 'Marketing');

Now, work to answer the following questions:

Select all employees in the Sales department.
Select all employees in the Finance department with a salary greater than $60,000.
Select all employees whose last name starts with 'D'.
Select all employees hired after January 1st, 2020.
Select all employees whose first name is either 'John' or 'Jane'.
Select all employees whose salary is between $50,000 and $70,000.
Select all employees whose email address contains 'company.com'.
Select all employees and include a computed field called 'FullName' which is the concatenation of first and last name.
Select all employees and include a computed field called 'SalaryPerMonth' which is the employee's annual salary divided by 12.
Select all employees and include a computed field called 'YearsOfService' which is the number of years the employee has been with the company.

TyroCity

DATA 210 Week 3 Class 1 Resources

Top comments (0)