SQL

Structured Query Language

the "standard" language for querying (and inserting, updating, etc.) a Relational Data Base.

joins https://dataschool.com/how-to-teach-people-sql/sql-join-types-explained-visually/

  • sigh I still use the "classic" syntax for (inner) joins, rather than actually using the join argument

Tips

correlated subquery (subquery takes field value from outer query as a param): using exists (in MySql)


old example?

consider a "software management system" that keeps the following tables:

  • .project(project-id,title,clint's-id,date of starting expected duration).
  • .clints(clints-id,clint-name,clint-address,clint-e-mail-address).
  • .person9person-id,person-name,person-address,person-e-mail-address).
  • project-person(project-id,person-id).

an employee may be working on more than one project at a time and one project at a time and one project may have more than one employee working on it. creat appropriate referential integrity constraints and create the tables.

write and run the following SQL queries on the tables:

  • Find the client-id and client name of the client who have given maximum number of projects to the company.
  • find the list of all the project names on which an employeenamed"Dushyant" has worked.
  • find the list of names of all those staff members who have worked on all the projects of client "TATA".
  • find the projects which will be comleted this week(Make and state suitable assumptions).
  • Find all the clients who have not given any project. Also find the person who is not working on any project at all.

Edited:    |       |    Search Twitter for discussion