Main content
Computer programming
Course: Computer programming > Unit 3
Lesson 4: Modifying databases with SQLUsing SQL to update a database
As we've mentioned throughout this course, there are many times we might find ourselves using SQL or a SQL-like query language on a database. We can think of some uses as "read-only operations" and other uses as "read/write operations".
An example of a "read-only operation" is a data analysis on a data dump from some app or research study. For example, if I was a data scientist working for a daily diary, I might query what percentage of users eat ice cream on the same day that they run, to understand if exercise makes people want to reward themselves:
SELECT * FROM diary_logs WHERE
food LIKE "%ice cream%" AND activity LIKE "%running%";If I'm doing a data analysis like that, then pretty much everything I'm doing is a
SELECT - it's all read only. We're not creating any new data, we are just querying existing data. We need to get very good at SELECT queries, but we don't need to know how to create tables, update rows, and all of that.An example of "read/write operations" is a software engineer creating the backend for a webapp. For example, if I was the software engineer working on the health tracker, I might write code that knows how to insert a new daily log in the database every time a user submits a form:
INSERT INTO diary_logs (id, food, activity)
VALUES (123, "ice cream", "running");I would probably be issuing that SQL command from inside a server-side language, likely using a library to make it easier to construct the commands. This is what that insertion would look like if I was using Python with the SQLAlchemy library:
diary_logs.insert().values(id=123, food="ice cream", activity='running')I would also need to write SQL to modify the database if the user edited their daily log, deleted it, or even deleted their account. If the team decided to add more features to the user's daily log, like an emotion column to track how happy while they were eating ice cream and running (my hypothesis: very happy), I would need to write SQL to modify the table schema itself.
Those are all "write operations", and they are necessary if we're going to use a database to store and update data for an app.
INSERT is relatively safe, because all it does is add data, but operations like UPDATE, DELETE, DROP, or ALTER can be much more dangerous, because they are updating existing data. That's why it's important to really understand those well, and use them carefully. Keep going to learn how to use them!Want to join the conversation?
What does a library do? Is it like a list of saved commands/queries?(67 votes)
Good question. Yes, a library is a set of pre-written commands that are used to make life easier for the developer. Many companies and individuals create their own libraries in order to make everyday programming tasks simpler. Some libraries contain very useful commands and algorithms that would normally take hundreds of lines to write out, so they help programmers save time and energy. Most modern programming languages (Python, Java, HTML/CSS, etc.) have libraries written for them that can be found online or accessed publicly. For example, Processing.js is a library written for JavaScript that is used in the drawing environment here on Khan Academy.(177 votes)
The Python code has "running" enclosed in single quotes whereas "ice cream" is in double quotes.
Is this a mistake ?(26 votes)
In Python single and double quotes are interchangeable, so no.(68 votes)
If the schema is changed, that all existing data will be affected. Is there any way to avoid modifying all data one by one? Thank you.(13 votes)
If you mean modifying the existing data manually, then there is a way - probably depending on your system/environment. I use sql through rails, so when the db schema changes, it's through a migration. Another migration can update any existing records so you don't break anything(16 votes)
Hi everyone,
I'm taking this Intro to SQL course because I want to get a job as a Research Analyst in Institutional Research and Planning at universities and colleges ( so far, most of the Research Analyst positions require knowledge of SQL). Is completing this course enough to help me get this type of job, assuming that I have all the other skills they want, e.g., stats, writing, etc? In other words, is the content in this course advanced enough for this position that I could impress my potential employers with my knowledge of the stuff we learned here? Any advice from people who have experience working in Institutional Research and Planning would be greatly appreciated. Many thanks!(8 votes)- As far as languages go, SQL is not heavily involved. These courses will lay a solid foundation for your understanding and allow you to pick up where any employer will begin to train their people. As someone who trained SQL equivalent to people who had exposure and those who didn't, these courses are enough to get your foot in the door. Training people to think about database structure and data retrieval in a 4 day class is really hard if they haven't been exposed to the concepts. Best of luck!(22 votes)
- Could you keep your program safe by hiding the screen?(3 votes)
As far as I know you can't hide the screen or code on Khan Academy. If you mean in an actual website with SQL, making a database doesn't actually print it on the screen (that would mean trouble for banking companies for example) so I don't believe that hiding the screen to keep your program safe would be necessary. In real websites you do need to try and keep your data secure by protecting against measures such as SQL injections. (https://xkcd.com/327/ is a funny comic illustrating that exact thing happening).(11 votes)
whatever we learnt using khan academy is enough before going to the IT company?(1 vote)- At some point you need to accept learning by doing rather than studying. keep grinding.(12 votes)
- Where's a good place to look that provides strategies on creating efficient and meaningful SELECT queries?(3 votes)
what is wrong with this code :
CREATE TABLE persons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fullname TEXT,
age INTEGER);
INSERT INTO persons (fullname, age) VALUES ("Bobby McBobbyFace", "12");
INSERT INTO persons (fullname, age) VALUES ("Lucy BoBucie", "25");
INSERT INTO persons (fullname, age) VALUES ("Banana FoFanna", "14");
INSERT INTO persons (fullname, age) VALUES ("Shish Kabob", "20");
INSERT INTO persons (fullname, age) VALUES ("Fluffy Sparkles", "8");
CREATE table hobbies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person_id INTEGER,
name TEXT);
INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (1, "coding");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
INSERT INTO hobbies (person_id, name) VALUES (3, "rowing");
INSERT INTO hobbies (person_id, name) VALUES (3, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (4, "coding");
INSERT INTO hobbies (person_id, name) VALUES (4, "dilly-dallying");
INSERT INTO hobbies (person_id, name) VALUES (4, "meowing");
CREATE table friends (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person1_id INTEGER,
person2_id INTEGER);
INSERT INTO friends (person1_id, person2_id)
VALUES (1, 4);
INSERT INTO friends (person1_id, person2_id)
VALUES (2, 3);
SELECT persons.fullname, hobbies.name
From persons
JOIN hobbies
ON persons.id = hobbies.person_id;
SELECT a.name, b.name
From friends
JOIN hobbies as a
ON friends.person1_id = a.id;
JOIN hobbies as b
ON friends.person2_id = b.id;
thank you(2 votes)- try this
select a.fullname, b.fullname from friends
join persons a
on friends.person1_id = a.id
join persons b
on friends.person2_id = b.id(6 votes)
- I'm studing for data science but my way now is to be a developer to understand everything from cero, in SQL I hear about DCL statements, what are those?(2 votes)
DCL (data control language) statements are used in some flavors of SQL to mange users' ability to perform tasks in SQL. DCL statements grant or revoke permissions based on user IDs. This is to minimize the possibility of data being accidentally deleted or changed.
SQLite, which we use on Khan Academy, does not use DCL commands. SQLite does not employ user IDs. The ability to edit data in SQLite depends on file system permissions.(6 votes)
- oracle is a computing program that uses sql, plsql, isql and sqlplus do you have a site based on that? thank you.(3 votes)