SQL Basics

-

SQL Basics

Viewing existing databases:

To view existing databases use : SHOW DATABASES;



 

 

 

 

 

 

 

Creating A Database: 

 To create a new database use: CREATE DATABASE [IF NOT EXISTS]  database_name;

  • The database_name is the name of the desired database we want to create, which should be meaningful and descriptive.
  • The IF NOT EXISTS is an optional statement which gives an error while creating a new database if it already exists. (But if the IF NOT EXISTS clause is used in MariaDB it will return a warning instead of an error if the specified database already exists.)

Eg. CREATE DATABASE JOHNDRIPPER; 




Now if we run SHOW DATABASES; again we will see the new database johndripper there.









Selecting a Database:

To select a database use the query: USE database_name;

Eg. USE JOHNDRIPPER;


 



Removing a Database:

Removing a database deletes the entire data and all its related objects associated with it permanently therefore use it cautiously.

To delete a database use the statement:

DROP DATABASE [IF EXISTS] database_name;

database_name is the name of the database we want to remove.

IF EXISTS clause is optional, it prevents us from removing the database that does not exist.

Eg. DROP DATABASE JOHNDRIPPER;





MYSQL TABLES:

A table consists of rows and columns. A column denotes a field/attribute and rows contain the data for each unit/record/tuple of the fields. The number of rows denotes the Cardinality of the table. The number of columns denotes the Degree of the table.

Rules for naming a table:
  • A table name can have a maximum of 30 characters.
  • It can contain alphanumeric characters viz A-Z, a-z and numbers 0-9.
  • A table name must begin with an alphabet.
  • Use of special character _(underscore) is allowed which is used for joining two words.
  • Reserved words are not allowed. eg CREATE, SELECT, ALTER.

 Creating a Table:

 To create a table use: CREATE TABLE [IF NOT EXISTS] <table_name> (
<column_name> <data_type> [size] [PRIMARY KEY] [NOT NULL | NULL],
- - - - - -,
);

column_name is the name of the column. It needs a data type and size eg COLUMN_1 VARCHAR(20) where column_1 is the name of the column varchar is a data type containing character and with a max size of 20.
The NOT NULL or NULL option indicates whether the column accepts NULL values or not.
PRIMARY KEY means that the column only accepts unique value.



Comments

Post a Comment

Popular posts from this blog

Chapter 05 - File Handling

-
Chapter 05 - File Handling Type C: Programming Practice/ Knowledge based Questions 1. Write a program that reads a text file and creates another file that is identical except that every sequence of consecutive blank spaces is replaced by a single space. 2. A file sports.dat contains information in following format: Event ~ Participant. Write a function that would read contents from file sports.dat and create a file named Atheletic.dat copying only those records from sports.dat where the event name is "Athletics". 3. A file contains a list of telephone numbers in the following form: Arvind 7258031 Sachin 7259197 The names contain only one word the names and telephone numbers are separated by white spaces. Write a program to read a file and display its contents in two columns. 4. Write a program to count the words 'to' and 'the' present in a text file "Poem.txt". 5.Write a program to count the number of upper-case alphabets ...
Read more

Dictionary (Python)

-
DICTIONARY 1. Repeatedly ask the user to enter a team name and how many games the team has won and how many they lost. Store this information in a dictionary where the keys are the team names and the values are the lists of the form [wins, losses]. (a) Using the dictionary created above, allow the user to enter a team name and print out the teams winning percentage. (b) Using the dictionary, create a list whose entries are the number of wins of each team. (c) Using the dictionary, create a list of all those teams that have winning records. team_dict ={} winningList=[] winningTeam=[] numofTeams=int(input("Insert Number of Teams: ")) for x in range(numofTeams): teamName=input('Team Name: ') winLossList=[] wins = int(input('Wins: ')) winningList.append(wins) losses=int(input('Losses: ')) if(wins>losses): winningTeam.append(teamName) winLossList.append(wins) winLossList.append(losses) team_dict[teamName] = winLossList print(...
Read more

Tuple (Python)

-
TUPLE 1. Write a Python program that creates a tuple storing first 9 terms of Fibonacci series. fib=(0,) n=int(input('Please enter a number ')) a,b=0,1 for i in range(n): a, b = b, a+b fib=fib+(a,) print(fib) 2. (a) Write a program that receives the index and returns the corresponding value. fib=(0,) n=int(input('Please enter a number ')) a,b=0,1 for i in range(n): a, b = b, a+b fib=fib+(a,) print(fib) readInput=int(input("Enter an index ")) print("Element at index ",readInput," is : ",fib[readInput]) (b) Write a program that receives a Fibonacci term and returns a number telling which term it is for instance, if you pass 3, it returns 5, telling it is 5th term; for 8, it returns 7. fib=(0,) n=int(input('Please enter a number ')) a,b=0,1 for i in range(n): a, b = b, a+b fib=fib+(a,) print(fib) readInput=int(input("Enter a fibonacci term: ")) print(fib.index(readInput)+1) 3. Write a pr...
Read more