MySQL/tasks/Wiederholung-01.12.2023.sql

37 lines
869 B
SQL

USE tutorial;
/* Cartesian product (every row combined with every other row) and filter out rows */
SELECT
em.id AS 'Employee ID',
em.surname AS 'Surname',
em.forename AS 'forename',
at.id AS 'Title ID',
at.name AS 'Title'
FROM
Employee AS em,
AcademicTitle AS at
WHERE
em.surname LIKE 'Ma%'
AND at.id = em.academicTitleId;
/* only select rows which have a matching (true) condition */
SELECT *
FROM Employee AS em
INNER JOIN AcademicTitle AS at
ON em.academicTitleId = at.id
WHERE
em.surname LIKE 'Ma%';
SELECT
em.surname,
em.roomId AS 'Employee Room',
room.id AS 'Room ID',
room.name AS 'Room name'
FROM Employee as em
INNER JOIN Room as room ON em.roomId = room.id
WHERE room.name LIKE 'a%';
SELECT
count(*) AS Anzahl
FROM Employee em
LEFT JOIN PostalLocation post ON post.id = em.postalLocationId;