SQL - Data Definition Language

[@gcreddy]

SQL- Data Definition Language Class Notes: SQL Tutorial 5: Data Definition Language --------------------------------------------- SQL (Structured Query Language) is for Database Developers, Database Administrators, and Database testers. Three main subsets of SQL: i) Data Definition Language ii) Data Manipulation Language iii) Data Control Language ------------------------------ Database Engine is required to practice / use SQL, we can use any Database engine like Oracle, MS SQL Server or MySQL etc... ---------------------------------------- Overview i) Database Management System ii) Database Engine iii) Database Server iv) Database v) Table and Record i) Database Management System > Database Management System is designed to allow the definition, creation, querying, update and administration of Databases > It is a Computer Software Application that interacts with the user, other applications, and the database itself to capture and analyze the data ii) Database Engine Software that stores and retrieves data in a database. It may be self-contained program or the part of DBMS that performs the storage and retrieval operations. iii) Database Server Database Management Systems provide Database server functionality, database server provides database services to other computer programs or computers. iv) Database > A Database is a Systematic collection of data > Databases support storage and manipulations of data > Databases make data management easy v) Table and Record > A Table is a predefined format of rows and columns that define an entity > Each column contains a different type of attribute and each row corresponds a single record. --------------------------------------------------------- Data Definition Language Data Definition Language Commands are used to Create, Modify, and Drop the Structure of Database Objects like Table, View, and Index etc... Data Definition Language Commands and Operations ---------------------------------------- Important DDL Commands 1) Create 2) Alter 3) Drop 4) Truncate 5) Rename ---------------------------------------- Important DDL Operations 1) Create a Database 2) Use Database 3) Rename a Database 4) Drop Database 5) Create a Table 6) Rename Table 7) Add a Column to exiting Table 8) Add multiple columns to existing Table 9) Modify an existing column 10) Rename a Column 11) Drop a Column 12) Truncate a Table 13) Drop a Table SQL Tutorial Download and Install MS SQL Server Express Edition (It is Free Edition) and practice SQL Commands and Operations. ---------------------------------------- 1) Create a Database Syntax: Create Database databaseName; Example: Create Database gcreddyDB; ---------------------------------------- 2) Use Database Syntax Use databaseName; Example: Use gcreddyDB; ---------------------------------------- 3) Rename a Database Syntax Alter Database databaseName Modify Name = newdatabseName; Example: Alter Database gcreddyDB Modify Name = hyderabad Or Alter Database gcreddyDB Modify Name = hyderabad ---------------------------------------- 4) Drop a Database Syntax: Drop Database databaseName; Example: Drop Database gcreddyDB; ---------------------------------------- 5) Create a Table Syntax: Create Table tableName ( column1_name dataType(size), column2_name dataType(size), . . . ); Example: Create Table Students ( STID int, STName char(50), ); ------------------------------------ View Table info Select * from Students View Table Schema Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Students'; ---------------------------------------- 6) Rename Table Syntax: EXEC sp_rename 'old_tablename', 'new_tablename'; Example: EXEC sp_rename 'Students', 'newStudents'; ---------------------------------------- 7) Add a Column to existing Table Syntax: Alter Table table_name add column_name dataType(size); Example: Alter Table newStudents add City char(50); ---------------------------------------- 8) Add multiple columns to an existing Table Syntax: Alter Table table_name add column1_name dataType(size), column2_name dataType(size); Or Alter Table table_name add column1_name dataType(size), column2_name dataType(size), . .; Example: Alter Table newStudents add add1 char(100), add2 char(70); Or Alter Table newStudents add add3 char(100), add4 char(70), add5 char (100), phone int; ---------------------------------------- 9) Modify an existing column Syntax: Alter Table table_name Alter Column column_name dataType(size); Example: Alter Table newStudents Alter Column add1 varchar(150); ---------------------------------------- 10) Rename a Column Syntax: EXEC sp_rename 'table_name.old_column_name', 'new_colum_name'; Example: ExEC sp_rename 'newStudents.phone', 'mobile' ---------------------------------------- 11) Drop a Column Syntax: Alter Table table_name Drop Column column_name; Example: Alter Table newStudents Drop Column City; ---------------------------------------- 12) Truncate a Table Truncate Table command is used to delete complete data from an existing table Syntax: Truncate Table table_name; Example: Truncate Table newStudents; ---------------------------------------- 13) Drop a Table Drop Table command is used to delete complete Table (Data and Table Structure) from the Database. Syntax: Drop Table table_name; Example: Drop Table newStudents; -----------------------------------------------------------------

[@gcreddy]

Class Note: www.gcreddy.com/2016/11/sql-data-definition-language.html

[@sunrise-l5v]

Thank you very much Prof GC Reddy Sir it,s was really helpful .

[@gcreddy]

Welcome...

[@wsglobal2023]

Thank you! See you in the next class.

[@AshwiniGBBarki]

Hello Sir, Myself Ashwini am fallowing your SQL tutorials and am need of DML and DCL tutorials. So please send it sir. Really am getting vry usefull from your tutorial videos.

[@techwithnasir2782]

thank you very much Sir the way you teach is really fantastic!

[@gcreddy]

Welcome

[@gcreddy]

Selenium Step by Step Videos kzbin.info/aero/PLyGqUe6Oa_5Elc-Dv9jPzHKDx-m2GvMOd UFT (formerly QTP) Step by Step Videos kzbin.info/aero/PLyGqUe6Oa_5HAIz0A-p_H48-cKXwNK0ZA SQL Step by Step Videos kzbin.info/aero/PLyGqUe6Oa_5Hcb_5QqXIsMZhvsm7Bo_-Z

[@marketvillens582]

great session

[@gcreddy]

Welcome...

[@Lena-of7wd]

Thank you, this was helpful!

[@gcreddy]

Welcome

[@anirtejkasba3462]

Hello sir, I have one question, requesting to let us know how to add column in between the columns,ex: if we add fname ,age, mobile...and know if we would like to add lname after fname what would be the command for that to add , thanks in advance 🙏

[@zubairqureshi7365]

Hi sir, command for creating data base is successfully executed but executed data base is not showing under data bases folder. pls reply

[@konduruvijaya4963]

thank you so much

[@rajrachaprolu7777]

sir may i know where is the tutorial 6,7,8 of SQL

[@rajesh6262]

I am not able to find the link for SQL Tutorial 6. Please help.

[@sarathgvnd7472]

Sir, is both truncate and drop commands has same functionality in DDL?

[@chandup9382]

Truncate command is to remove all records from the Table, whereas Drop command is used to drop Databases and Database objects. Hope it's clear now brother :-)

[@shaileshchaudhary6621]

alter database sshk modify name =shailesh; why not working in MySQL please tell me Details

[@viswanathareddy1309]

sir why you use here exec and sp i think another way is there sir

[@srikantsamal2389]

Sir where i can find 1st to last video one by one

[@gcreddy]

kzbin.info/aero/PLyGqUe6Oa_5Hcb_5QqXIsMZhvsm7Bo_-Z

[@aliatozchannel8468]

Sir please send me SQL tutorial in one clip I want to learn it

[@user-vl2qu6tp4h]

Hi sir trust your doing well!! You are doing nice job must appreciated I'm following your videos of selenium and SQL, Please can you upload DML and DCL commands also I'm searching for it. Thanks a lot sir..

[@gcreddy]

Welcome

[@rameshgujjala9272]

Sir y ur repeating same concepts again and again 🤔🤔🤔 better discuss other concepts also thank u sir