Difference between revisions of "SQL Tutorial"
(→Reference: how to...) |
|||
| (14 intermediate revisions by the same user not shown) | |||
| Line 17: | Line 17: | ||
; [[Self join|9 Self join]]: In which we join Edinburgh bus routes to Edinburgh bus routes. | ; [[Self join|9 Self join]]: In which we join Edinburgh bus routes to Edinburgh bus routes. | ||
; [[Tutorial_Quizzes|10 Tutorial Quizzes]]: Test your knowledge with multiple choice quizzes | ; [[Tutorial_Quizzes|10 Tutorial Quizzes]]: Test your knowledge with multiple choice quizzes | ||
| + | ; [[DDL Student Records|11 Tutorial Student Records]]: Creating a database | ||
| + | ; [[DDL General Election|12 Tutorial DDL]]: Instructions on how to import data and analyse it in mysql | ||
</div> | </div> | ||
<div class='refblock'> | <div class='refblock'> | ||
| Line 36: | Line 38: | ||
: How to find out what tables and columns exist. How to count and limit the rows return. | : How to find out what tables and columns exist. How to count and limit the rows return. | ||
;8 [[Hacks Reference|SQL Hacks]] | ;8 [[Hacks Reference|SQL Hacks]] | ||
| − | : | + | : Some SQL Hacks, taken from "SQL Hacks" published by O'Reilly |
| + | ;9 [[SQL with PHP|Using SQL with PHP on Amazon EC2 servers]] | ||
| + | : Video tutorials showing how to run MySQL, PHP and Apache on Amazon's EC2 cloud servers. | ||
| + | : Simpler instructions for students following module [[INF08104]] at Edinburgh Napier University | ||
| + | ;10 [[Transactions|An introduction to transactions]] | ||
| + | : Video tutorials showing how sessions can interfere with each other and how to stop it. | ||
| + | ;11 [[SQL with C#|Using SQL with C# in Visual Studio]] | ||
| + | : Video tutorials showing how to connect to a database in Visual Studio. | ||
| + | |||
</div> | </div> | ||
<div class='resblock' style='clear:left'> | <div class='resblock' style='clear:left'> | ||
<div style='clear:left'></div> | <div style='clear:left'></div> | ||
| + | |||
==Assessments: More involved examples for confident users== | ==Assessments: More involved examples for confident users== | ||
Each assessment includes 15 questions graded easy, medium and hard. | Each assessment includes 15 questions graded easy, medium and hard. | ||
| + | ;[[Module Feedback]] | ||
| + | : Responses from students on their learning experience. | ||
| + | ;[[Help Desk]] | ||
| + | : Calls to a support desk | ||
| + | ;[[Guest House]] | ||
| + | : Managing bookings for a hotel | ||
;[[AdventureWorks|Adventure Works]] | ;[[AdventureWorks|Adventure Works]] | ||
: An example based on a wholesaler (based on MicroSoft's sample database) | : An example based on a wholesaler (based on MicroSoft's sample database) | ||
| Line 52: | Line 69: | ||
;[[Congestion Charging]] | ;[[Congestion Charging]] | ||
: Monitoring and charging cars going in and out of London | : Monitoring and charging cars going in and out of London | ||
| + | |||
| + | ==Challenges== | ||
| + | *[[White Christmas]] | ||
==SQL Resources== | ==SQL Resources== | ||
Revision as of 12:35, 3 October 2017
Learn SQL using: SQL Server, Oracle, MySQL, DB2, and PostgreSQL.
Tutorials: Learn SQL in stages
- 0 SELECT basics
- Some simple queries to get you started
- 1 SELECT name
- Some pattern matching queries
- 2 SELECT from World
- In which we query the World country profile table.
- 3 SELECT from Nobel
- Additional practice of the basic features using a table of Nobel Prize winners.
- 4 SELECT within SELECT
- In which we form queries using other queries.
- 5 SUM and COUNT
- In which we apply aggregate functions. more the same
- 6 JOIN
- In which we join two tables; game and goals. previously music tutorial
- 7 More JOIN operations
- In which we join actors to movies in the Movie Database.
- 8 Using Null
- In which we look at teachers in departments. previously Scottish Parliament
- 8+ Numeric Examples
- In which we look at a survey and deal with some more complex calculations.
- 9 Self join
- In which we join Edinburgh bus routes to Edinburgh bus routes.
- 10 Tutorial Quizzes
- Test your knowledge with multiple choice quizzes
- 11 Tutorial Student Records
- Creating a database
- 12 Tutorial DDL
- Instructions on how to import data and analyse it in mysql
Reference: how to...
- 1 SELECT
- How to read the data from a database.
- 2 CREATE and DROP
- How to create tables, indexes, views and other things. How to get rid of them.
- 3 INSERT and DELETE
- How to put records into a table, change them and how to take them out again.
- 4 DATE and TIME
- How to work with dates; adding, subtracting and formatting.
- 5 Functions
- How to use string functions, logical functions and mathematical functions.
- 6 Users
- How to create users, GRANT and DENY access, get at other peoples tables. How to find processes and kill them.
- 7 Meta Data
- How to find out what tables and columns exist. How to count and limit the rows return.
- 8 SQL Hacks
- Some SQL Hacks, taken from "SQL Hacks" published by O'Reilly
- 9 Using SQL with PHP on Amazon EC2 servers
- Video tutorials showing how to run MySQL, PHP and Apache on Amazon's EC2 cloud servers.
- Simpler instructions for students following module INF08104 at Edinburgh Napier University
- 10 An introduction to transactions
- Video tutorials showing how sessions can interfere with each other and how to stop it.
- 11 Using SQL with C# in Visual Studio
- Video tutorials showing how to connect to a database in Visual Studio.
Assessments: More involved examples for confident users
Each assessment includes 15 questions graded easy, medium and hard.
- Module Feedback
- Responses from students on their learning experience.
- Help Desk
- Calls to a support desk
- Guest House
- Managing bookings for a hotel
- Adventure Works
- An example based on a wholesaler (based on MicroSoft's sample database)
- University Timetables
- Schedules for lectures and tutorials
- Musicians
- Bands, Concerts, Performances
- Dressmaker
- Making and ordering dresses
- Congestion Charging
- Monitoring and charging cars going in and out of London
Challenges
SQL Resources
- Select Basics - Answers
- Select from World - Answers
- See if you can spot the pattern
- SQL User Manuals
- MimerMySQL Oracle SQL Server Access DB2 Sybase Postgres SQLite
- Online copies of implementation specific user manuals.
- Starting SQL
- [ MySQL | Oracle | SQL Server | DB2 | Postgres | Ingres ]
- How to connect to the server and execute SQL statements.
- Programming with SQL
- [ SQL in perl on the Web | SQL from Java ]
- Error Messages
- Some error messages from SQL engines
- Temporary Assessment section, before the main menu is finished.
- Assessment Temp
