MySQL/tasks/joins.sql

36 lines
1.2 KiB
SQL

USE tutorial;
SELECT positionId, forename, surname FROM Employee;
/* select only one of duplicates */
SELECT DISTINCT streetName FROM Employee;
/* add further conditions with where */
SELECT surname, EUR_GrossSalary FROM Employee
WHERE academicTitleId = 1
AND (EUR_GrossSalary BETWEEN 40000 AND 50000)
AND EUR_GrossSalary != 46000;
/* filter out non existing entries */
SELECT * FROM PostalLocation
WHERE cityDistrictName NOT LIKE ''
OR cityDistrictName IS NOT NULL;
/* check if something is a member of a list */
SELECT * FROM Employee WHERE academicTitleId IN (0, 1);
/* in with a sub query */
SELECT * FROM Employee WHERE academicTitleId IN (
SELECT academicTitleId FROM AcademicTitle WHERE academicTitleId > 1
);
/* check string for pattern */
/* %: zero or more characters */
/* _: one character */
SELECT surname FROM Employee WHERE surname LIKE 'A%';
/* where with regex */
SELECT surname FROM Employee WHERE REGEXP_LIKE(surname, '^[aB].*');
/* selects employees with have academic title of 1 or (exclusive) surname starts with B */
SELECT * FROM Employee WHERE academicTitleId = 1 XOR surname LIKE 'B';