Topics
Welcome to the Santa Rosa Junior College class on Structured Query Language, or SQL. This class is designed
- to introduce MySQL server as a tool to manage information in a relational database,
- to understand the SQL Database Language
- to explore how to design a database and develop a database-driven web application.
We will be following the Learning MySQL textbook liberally throughout this half semester, supplemented by pages on this website.
This week we focus on setting up our tools and exploring MySQL Workbench and the SQL Select statement.
Assignment – Due April 3
- Read these articles on wikipedia.org: SQL & MySQL
- Read my Getting Started with MySQL post
- Download & Install MySQL Workbench
- In MySQL Workbench, create a Database Connection using your ADMIN account, following the instructions in the blog post. (Our SRJC setup is such that only the ADMIN account has the privilege to create tables.)
- Download this music.sql file.
- Open a database session in MySQL Workbench. Using the Scratch Pad, enter (type in) and execute (click the lightening bolt icon) the SQL command “use xxxxxxxx” (where xxxxx is your database name. It is the same as your username. Mine is jperetz.) This will activate your database, i.e. all future commands will execute within that database.
- Open the music.sql file and execute it (click the lightening bolt).
- Using MySQL Workbench’s scratch pad, follow the textbook from page 137 – 162. There are many examples of the SQL Select statement using the four tables in the music database.
- Email me a list of your MySQL accounts and passwords. In the email, include answers to questions 1 and 2 on page 176 of the textbook. Include the answer to the question and the SQL statement that you used to determine the answer.
- Leave a comment on this blog post page. In one short paragraph, introduce yourself to the class., include your name, your experience with databases and objectives for taking this class, as well as anything else you’d like to share. In a second short paragraph, tell us about your experience with this assignment. Did you have any problems? What did you learn? Are you ready to learn more? Any resources to share with the class.
Greetings everyone. My name is Seth Schwebs. I have been working with DBs for about 10 years. Starting with MS access and transitioning to MySQL about 5 years ago. So far everything is self taught so my goal with this class is to firm up my knowledge and plug any holes I have. My main work is in the vineyards. We use databases to keep track of all kinds of info ranging from soil conditions to irrigation amounts to climate data.
I found this lesson pretty straight forward. On page 145 of the text, in the second SQL statement there is a missplaced -> that through me for a little loop.
Seth
0 4 08:21:01 CREATE TABLE track (
track_id SMALLINT(3) NOT NULL DEFAULT 0,
track_name CHAR(128) DEFAULT NULL,
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 0,
time DECIMAL(5,2) DEFAULT NULL,
PRIMARY KEY (artist_id,album_id,track_id),
FOREIGN KEY (artist_id) REFERENCES artist(artist_id),
FOREIGN KEY (album_id) REFERENCES album(album_id)
) Error Code: 1005
Can’t create table ‘densley.track’ (errno: 150) 0.062 sec
I got an error can’t create table densley.track from the music.sql file executing.
Don- Does the table already exist? Are you logged into the admin account
Hi Jay,
Since my “densley” database was pre-existing, it was creating the tables using the other engine, InnoDB. So I modified the music.sql adding the ENGINE = MYISAM before the last semi-colon in each of the CREATE TABLE statements. The music.sql script now completes with no errors.
Don Ensley
I’ve been working with computers for too many years to count. Not an expert at any thing, more of a generalist who gets to know the things I need to in order to get projects done. I am currently responsible for the technology at an internet retailer and have been doing some work on the MS SQL Server database. So, I am taking this class to expand my knowlege of SQL.
I had a couple of challenges with this lesson, mostly it was learning about the toolset within MySQL Workbench. Also, I got some error messages from the Workbench which requested a submission as a bug report – so I’ve been exchanging info with someone in the MySQL Workbench world.
I also had a challenge exporting the recordset output to Windows. The export function doesn’t have a Browse feature, but if the full path is entered (e.g. C:SQLfilename), it works.
The Export function seems to work fine on Mac OSx.
MySQL Workbench tool has many features to explore (and some that may not always work perfectly, it is pretty buggy software). Given the scope of our class, we are not going to focus much on the Workbench tool itself. We will mainly use it as way to enter SQL to the server. We will also get to explore some of the design features in a few weeks.
Hello everyone,
My name is Brian Tietz, I’m responsible for making live changes to several databases running on both MySql and MsSql for work. My objectives are to hone my skills in SQL and gain confidence and speed in writing SQL queries. One thing I’d like to ask everyone is if you believe the industry is moving more towards an object oriented way of interacting with database vs writing SQL statements directly? (ie: http://www.linqpad.net/WhyLINQBeatsSQL.aspx)
For this assignment I had to turn off some music I was listening to and read over our database schema some more before answering, but other than that I didn’t find myself in too much trouble and I’m definitely ready to learn more.
The only resource I can think of right now to share would be: W3School basic SQL tutorials at: http://w3schools.com/sql/default.asp
Hi everybody,
My name is Francesca and I am a GIS (geographic information systems) tech and use SQL queries with the ArcGIS software all the time. GIS uses relational databases with added spatial data. I’ve been fumbling around with the more complex queries because I don’t know how to structure them properly. I’m really looking forward to having more skills at my fingertips.
The first week’s homework was fine once I got past the intial downloads and hooking up to the SRJC server.
Hello,
My name is Paul Clark. I am some what familiar with databases. As of right now I am in the Microsoft Access class and previously took PHP and MySQL. My reason for taking this class is to satisfy a requirement for some of the web design certificates. I enjoy programming in many different languages and would love to find a job or internship soon that deals with programming.
I did not have really any trouble with this assignment. I didnt learn anything new from this assignment because compared to the stuff we did in the php class this is simple. I am interested to see if I learn anything new. One thing I did find interesting though was using MySQL Workbench, because it makes it a lot easier to control the database instead of the console.
My name is Don Ensley and I used SQL databases last semester in MS Access class, and my PHP programming class. I have also used SQL many year ago when I was a programmer. I’m taking this class to become more familiar and used to using SQL, and to prepare myself to use it on a daily basis in my next database programming position.
During this assignment, I installed the MySQL Workbench over an existing installation of MySQL Community Server. Since I was using both the workbench and server to access databases of the same name over the same port (port 3306 is the default), there was a problem in that the server on my machine at home would confuse the password so that I could no longer access it. I learned to change the port to use different ports as a way to resolve this problem. Also, the administrative account on the student server does not let me create databases, only to create tables on the existing database densley. This restriction forces me to use the same database name (densley) for all the applications on the server, such as database applications running in PHP for entirely different projects. The result is that the database has a whole bunch of tables in it, and I can’t really create a separate database “music” for the tables in the assignment. This assignment also showed me additional syntax variations, such as the multiline INSERT syntax, and gave me the chance to review the INNER JOIN syntax, where I need practice. I am ready to learn more.
Hi my name is Noah Freitas and I am in my last full semester of computer classes at the JC. Previous to this last Fall, I basically had no experience with databases whatsoever. I then took MS Access and PHP, where we made extensive use of the JC’s MySQL server. Since then I have played around a little more with MySQL (mainly through phpMyAdmin) and even a little MS SQL server. My objective for the course is to continue to refine my understanding of MySQL and to practice thinking in database structures, which I find comes much less naturally for me than application programming.
The assignment was fairly simple to complete; though I did have to think for a little while with the two questions. I also did enjoy using the Workbench scratch pad and found it fairly useful.
Hi, I’m Anna Narbutovskih. I’ve taken the Microsoft Access class and the PHP class, both of which provided the basics of SQL/database creation and query. I’ve not used SQL in any professional way yet but I’m fairly certain that I’ll need this knowledge as my web design career gains momentum. I’m taking this class to refresh and polish the SQL skills I learned in the PHP class.
I didn’t have any difficulties with this assignment but I was very grateful that I already had the MySQL database set up on the student server as I didn’t see anything in the instructions on how to do that. At first the MySQL Workbench was a bit confusing but it became easier very quickly.
I installed MySQL workbench but I it looks different from the snapshot in “Getting Started with MySQL” blog. And I am having issue finding the menu to connect to the Admin Account.
Never mind. I installed the wrong version MySQL workbench 5.0 OSS. I uninstall it and installed MySQL workbench 5.2 CE. That looks like the correct version since I get the same “Welcome” page as in the “Getting Started with MySQL” blog.
I got started with computers in 1975 when I decided to go to college and figure since my best subject was math
that I should study computer programming. This means I started out with using COBOL 68 and later COBOL 74. In those days
you didn’t have databases. We used index files. I did Inventory Control – Order Systems. In 1988 I got to move on to
using C language on a Unix box. In 95 I left that company and went on my own. Did ok till the economy when down in 99.
Than did self-employed Computer Tech work for about 6 years. But my real passion is programming so I’ve kept up on my own
being self taught. Now I’m back in college in pursue of at least 1 certificate for Web Programming.
Most of my experience with SQL is with MS-SQL and just little bit with My-SQL.
Hi everyone,
My name is Ryan Mansergh. My main experience with databases has been through projects that I’ve been involved with at work. I am currently taking the Access class here at the SRJC and I am taking this class to get more formal training in database design.
My initial problems with this assignment were mainly in getting my MySQL functionality up and running. I hadn’t run the “sqlme” command on my student.santarosa.edu account, so none of my accounts or directories were set up. I learned a fair amount about interacting with MySQL via the command line and through MySQL Workbench. I look forward to learning more about the inner workings of MySQL.
I didn’t have any problems with the assignment. But I had serious problems with getting my account setup. I still don’t know what I did or didn’t do to get it setup. And I’ve been over the instructions several times and didn’t find any specific instruction I was to do to get My-Sql setup on the server.
My only conclusion is even though I had and account on the Linux serve from my html classes that I should have made a request for an account again.
Any insight from anybody would be greatly appreciated.
Hello, my name is Wendy Allen and I am taking this class to further my knowledge and skills. I’ve had limited experience with sql queries but have other db application knowledge.
The assignment looks reasonable but I’m having problems establishing a connection via Workbench to the student sql database so I’m not able to complete the assignment!
Wendy = Send me your MySQL password information in an email I will see what the problem is. – Jay
Hi my name is Pla Gong. I am taking this class because it is a requirement for the JavaScript web certificate I am working toward. Also I am working for a private e-commerce company in Petaluma and I often have to use and write SQL script to get or update data in the Oracle database for the sites that the company manage. So by taking this class I am hoping to get more practice and to be comfortable with SQL.
As for the homework, I have downloaded MySQL workbench and but now I am stuck because I cannot connect to the DB server. I have sent you my information so let me know if you need anything else to figure out why I cannot connect.
Hello. My name is Mike Donahue and I’m an SQL student.
I was a software engineer in a former life, but that was years ago. I have a fair understanding of coding and databases. I’ve always liked ER diagrams as a way to document objects and relationships, database or otherwise, so I was pleasantly surprised to come across that in the text. My intermediate objective is to learn how to build an SQL/PHP/HTML host (maybe ASP/NET too) even if it ends up hosted by an ISP. My primary interest is GIS, but these days, web services are the basis of everything done by computers, so I think I need to know this stuff too.
Since I’m so green with SQL, this assignment took longer than I’d figured it would. Once I found the answer, it made perfect logical sense, but there was much trial and error between my first attempt and finally clearing the errors. The workbench is good, but it also takes practice. I found a snippets section on the workbench; now I need to figure out how to use it. Being able to copy in things that have worked before is a great timesaver.
Hey,
My name is Steven Avery, and I didn’t have much experience with databases before about a month ago, when I started working for a web development startup. I have been assisting in some heavy duty database maintenance since then, and when I heard about this class, I figured it would be a big help, especially since I was having so much trouble figuring out INNER JOIN clauses. Now they seem like cake.
As for my notes on this assignment, my first attempt at question 1 was to just return the highest number track from that album. However, this database uses track_id 0. Who does that? The first track on an album is never 0.
Hello, Ken Leef checking in. I’m a small shop IT admin for a construction company who dreams of being a software developer. Probably a little too old to make a major career change, but in this market… who knows? I’ve taken several web and programming classes at the JC and I work with MS SQL a little at work. My company used to have two full-time developers who custom built a project management web app on Flash/.NET/MS-SQL. Short story is that they’re gone and the app remains essential to our business. So I learn by trial and error mostly.
Assignment was pretty straight-forward and built on my existing knowledge. I found the wiki article on MySQL interesting for the history. I like taking classes like this especially for the “other” ways of doing things. While I might be able to find a solution to most problems via Google, it’s not always the best solution.
Hi, My name is Kathy Samoun. I am seeing a lot of familiar names in this class from other classes I have taken. As to my experience, I have a good working knowledge of PHP and use it almost every day with my web design clients as many of them are switching over to wordpress. Although I have a basic understanding of MySQL from taking PHP, I wanted to get more experience and a increase my skill level. I am also working on getting my PHP programming certificate and this class is part of that certificate. I’m also taking Access so it all is coming together:)
So far most of what we learned in this assignment I found as more of a review from my php class so I had no major issues. I do like the MySQL Workbench though! it is so much easier than using the command line. I’m looking forward to learning Workbench also!
Hi, I’m Jennifer Beckham, currently taking Access, haven’t taken PHP yet. I’m gathering maybe I should have from the comments because I know very little about SQL. I’m going for the PHP web programming certificate and taking some additional programming and weight lifting as well. I’ve used FileMaker Pro quite a bit. I have been working as a web designer for years and just starting to use more advanced CSS. I don’t like online classes, I cannot stay focused or motivated. This semester all my (computer) classes are online and I feel a bit like the white rabbit, I’m always late.
The assignment, first was confusing. Then my sign in didn’t work. I’ve already started late and can’t get done on time.
Hello, my name is Travis. I have developed some databases in MySql for clients, personal use, and for the PHP class here @ the JC. I am taking this class as a compliment to my job for use with Scribe, which is a MSSQL based program. I have also developed some small tools that use MsSql and MySql for data storage.
I hope to better understand the syntax and general practices while taking this class.
After taking a look at the MySQL workbench … I have found my new best friend ^_^. The db modeling features it has are awesome!
Here is my experience and comment about the assignment :
The direction on how to setup my SQL account was a little confusing. But once I got that sorted out and was able to connect to the DB server, I enjoyed the exerise on page 137-162 a lot. I learned a few things that I did not know. I found th in the SQL scratch pad if you do “–”, you can put comments after that. This helps so I can take notes along the way and save the things I typed in the scratch pad into a *.sql file.
I want to comment that for the 2 sql statements on page 149 as bwlow, I did not get same answer as the book. Here is my notes and the SQL
– p.149 the following SQL statement actually returns all except album_id=4, book says it returns empty set
select * from album where not album_id =4;
– p.149 the following SQL statement actually returns album_id=4, but the book says it returns all, even the album_id=4 itself
select * from album where not album_id !=4;