Introduction to SQL and MySQL: DDL, DML and DQL

Hello students, welcome to Kwickprep. A database holds lakhs of records, but how do you ask it a question? You use a special language called S Q L. Today we will learn what S Q L is and its three main command groups. Then we will open My S Q L and learn the everyday data types.

Let us start with the language itself. S Q L stands for Structured Query Language. A query is a question or an instruction that we send to the database. S Q L works with relational databases, where data is kept in tables. Its keywords can be typed in capital or small letters, and every statement ends with a semicolon.

So where does S Q L actually run? A D B M S runs as a server, which is a program that stores the data and waits for queries. You type your S Q L in a client program, the server runs it, and sends back the answer. My S Q L, Oracle, Postgre S Q L and S Q Lite all understand S Q L, with small differences.

S Q L commands are sorted into groups by the job they do. D D L, data definition language, builds or changes the structure, like creating a table. D M L, data manipulation language, adds, changes or removes the rows of data. D Q L, data query language, reads data out of the tables. Some textbooks put select inside D M L, so learn both ways.

Here is one D D L command. Create table student makes an empty table with two columns. Roll holds whole numbers, and name holds text of up to twenty characters. The table now exists, but it has no rows yet.

Here is one D M L command. Insert into student values one and Riya adds a row to the table. Notice that text values go inside single quotes, but numbers do not.

Here is one D Q L command. Select star from student reads the table. The star means all columns. The database answers with the roll number one and the name Riya.

Now let us get My S Q L ready on a computer. Download the free My S Q L Community Server from the official My S Q L website. Run the installer, and when it asks, set a password for the root user, who is the main administrator. Then open the My S Q L Command Line Client, or any terminal window. In a terminal, type My S Q L, dash U root, dash P, and press enter.

Here is what a first session looks like. Dash U root means log in as the user root, and dash P means ask for the password. After the password, you see the My S Q L prompt, ending in a greater than sign. The command show databases lists all the databases on the server. Select two plus three shows five, a quick test that everything works. Type exit to leave.

Beginners get stuck at the prompt in a few common ways. If you forget the semicolon, the prompt changes to an arrow, and My S Q L keeps waiting for more. Just type a semicolon and press enter to finish. Keywords work in capital or small letters, but capitals make queries easier to read. To leave My S Q L, type exit or quit.

Every column needs a data type, which tells the database what kind of value the column can hold. Char stores text of a fixed length, like the state code G J. Var char stores text of any length up to its limit, like a name. Int stores whole numbers, like forty two. Float stores numbers with a decimal point, like eighty five point five. Date stores a date as year, month and day, like the fifteenth of August, twenty twenty six.

Exams often ask the difference between char and var char. Char of ten always reserves ten characters, and fills the empty places with spaces. Var char of ten uses only as much space as the text needs, plus a tiny length marker. So use char when every value has the same size, like a six digit pin code. Use var char when lengths vary, like names and emails. Pause and predict. Riya has four letters, so how many characters does char of ten reserve? Ten, always.

Here is a result table that uses all five types. Roll is an int, because roll numbers are whole numbers. Name is var char of thirty, because names have different lengths. Section is char of one, because a section is always one letter. Percent is a float, and exam date is a date.

Let us revise what we learned today. S Q L is the language we use to talk to relational databases. D D L builds the structure, D M L changes the rows, and D Q L reads the data. We log in to My S Q L with dash U root and dash P, and end every statement with a semicolon. Char is fixed length, var char is variable, and int, float and date store whole numbers, decimals and dates.