In the last video, we wrote up the SQL to create three tables: CREATE TABLE users( user_id NUMBER, username VARCHAR2(50 CHAR) UNIQUE, CONSTRAINT users_pk PRIMARY KEY (user_id) ); CREATE TABLE projects( project_id NUMBER, Project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (project_id), CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username) ON DELETE CASCADE ); CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE, CONSTRAINT project_users_pk PRIMARY KEY (project_id, user_id) ); I'm going to increase the size of the users table a bit by adding a first and last name column. CREATE TABLE users( user_id NUMBER, username VARCHAR2(50 CHAR) UNIQUE, first_name VARCHAR2(50 CHAR), last_name VARCHAR2 (50 CHAR), CONSTRAINT users_pk PRIMARY KEY (user_id) ); But before we finish this design, we should consider indexing certain columns. What columns should we index? Well, as a reminder, the columns that are indexed by default are columns with the UNIQUE constraint, and those that are labeled as primary keys. Columns that are not indexed but often should be are those labeled as a foreign key. The column that jumps out the most to me is the creator column of the project table. It's the only foreign key that is not part of some index. Let's fix this by creating our first index. The way we do that is with the CREATE INDEX command. CREATE INDEX projects_creator_ix ON projects (creator) What naming convention are we following for the index? We are naming it by the table name, followed by an underscore, followed by the column, followed by an underscore, followed by an ix (for index). In this situation it does not apply, but if our foreign key column is labeled as UNIQUE, we can add the UNIQUE keyword like this: CREATE UNIQUE INDEX projects_creator_ix ON projects (creator) Now if you want to get rid of an INDEX, you can use this command: DROP INDEX projects_creator_ix Now, if we want to select data from the user table and the project table we can do that much faster. That's because the foreign key and column it references are both indexed and those are the columns we would do the join on. We will discuss how to do joins in a future video. So what are some potential problems with this database design? Overall, it is pretty good. With this design though we need to make sure there is no way for someone to update a user's username. In the next video we are going to discuss why. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me on Patreon! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 8769 Caleb Curry
This video is going to be a tutorial on how to create composite and compound keys. The difference between a composite and compound key is that a composite key can consist of any columns while a compound key has to consist of columns that are all keys themselves. We will be working with a compound key because we are going to be using the an intermediary table that has two foreign keys. The combination of both of the keys have to be unique. First, if we have any other CREATE TABLE commands, we are going to comment those out. We will space out the CREATE TABLE to have each column on a line, then we will add constraints as needed. CREATE TABLE project_users( project_id, user_id ) Now, let's add the data types: CREATE TABLE project_users( project_id NUMBER, user_id NUMBER } Now, what about some column attributes? I'm going to make both of the columns NOT NULL because we always want the rows to have a user and a project: CREATE TABLE project_users( project_id NUMBER NOT NULL, user_id NUMBER NOT NULL } Now, let's add the foreign key constraints. Now, what do we name these? We are going to add a primary key that covers both of these columns, so I'm going to be a sinner and not give these constraints names: CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id), user_id NUMBER NOT NULL REFFERENCES users (user_id) ) Now, the way we have it now is that if we delete a project in the project table, and there are any rows in the project_users table, it will throw an error and prevent deletion. I would prefer for it to also delete any project members. That would make sense because if you delete a project we want it to delete the associate between that project and certain users. The same goes for if we delete a user, we want their association with a certain project to be deleted. To do this, we need to add the ON DELETE command: CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE, ) Finally, let's learn how to create a compound or composite key. literally, the only difference is that you put a comma and add the second table inside of the parenthesis. CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE, CONSTRAINT project_users_pk PRIMARY KEY (project_id, user_id) ) Now, the combination of project_id and user_id cannot be null, is always unique, and has an index. The only thing we should do now is add a few indexes. We aren't done yet...In the next video we are going to figure out what columns would benefit from indexes and we'll add them to it. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 11100 Caleb Curry
To ask your doubts on this topic and much more, click on this Direct Link: http://www.techtud.com/video-lecture/lecture-foreign-key IMPORTANT LINKS: 1) Official Website: http://www.techtud.com/ 2) Virtual GATE: http://virtualgate.in/login/index.php Both of the above mentioned platforms are COMPLETELY FREE, so feel free to Explore, Learn, Practice & Share! Our Social Media Links: Facebook Page: https://www.facebook.com/techtuduniversity Facebook Group: https://www.facebook.com/groups/virtualgate Google+ Page: https://plus.google.com/+techtud/posts Last but not the least, SUBSCRIBE our YouTube channel to stay updated about the regularly uploaded new videos.
Views: 264737 Techtud
HAPPY VALENTINES DAY!!! Let's talk relationships.. This is the 6th video in your Oracle Database series. We are discussing database relationships which are a key feature of relational database management systems. We first discussed entities and attributes. I talked about how each entity is assigned a table and each attribute is a column within a table. We moved on to the three kinds of relationships. The first was one-to-one. This describes an entity and an attribute. A piece of data that is exclusive to an entity is, by definition, an attribute of that entity. This is stored in one table with the attribute being a column within this table. The second relationship is one-to-many. This relationship is between two entities. The way we properly store this in a database is using a foreign key in the child table. Remember, the child table is the entity on the many side of the one-to-many relationship. Every row within the child table will have a value for the foreign key that references a primary key in the parent table. This assumes that the foreign key field is not optional (NOT NULL). If the foreign key is optional, than a reference is not required but any reference must be valid. The third kind of relationship is a many-to-many relationship. In this situation, we need 3 tables. The many-to-many relationship is broken up into two one-to-many relationships. The intermediary table will associate each entity from one table with the appropriate entities in the other table. There is debate as to whether this table needs a primary key. This is because you can intact use the combination of two foreign keys as a primary key. This works because we will never have two duplicate rows within the intermediary table and the two foreign keys work as a compound key because of this. We finally discussed primary keys and foreign keys. Primary keys are used to keep each row inside of a table unique. If this key is a computer generated number it is known as a surrogate key, otherwise it is known as a natural key. Natural keys have real world meaning. For example, a social security number may work, or an email address (in some situations), etc. Whichever type of key you choose is solely up to you and/or the company you may be working for. HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://Twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 45302 Caleb Curry
The concept of an index is extremely important when managing a database. An index has the power of making your database very quick or it has the power to bog down your update, delete, and insert statements. The trick is to find a good balance. You will want to index any columns that are used continually in a select or a join. By default, all primary keys are indexed as well as any columns with the UNIQUE column attribute. I suggest you consider adding an index to your foreign keys as these will often be used in joins as well. You can actually create an index on a group of columns. This will allow you to search for data and return multiple columns. This is known as a composite index. There are many other things to database design...much more than we have covered. I have decided I will cover these as we go so that we can start applying what we learn practically. Support me: http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 17668 Caleb Curry
Donate!: http://bit.ly/DonateCTVM2. Primary key will automatically create an index used for database optimization! More content: http://CalebCurry.com Courses for Download: http://www.udemy.com/u/calebcurry/ Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://Twitter.com/calebCurry Subscribe (it's free!): http://bit.ly/PqPyvH Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 17488 Caleb Curry
Creating indexes is a very easy task, but before you go creating indexes on everything, you need to know that some columns are indexed by default. Specifically, any column that is labeled as the PRIMARY KEY or UNIQUE are indexed by default. That means that if you have a UNIQUE column, you do not need to worry about adding an INDEX to it. The columns that you will want to pay special attention to are any foreign keys or columns that you are going to use very frequently. We are going to create an index on our SpeciesID column in our Animals table. This is a foreign key that I'm likely going to use very frequently. CREATE INDEX IX_AnimalsSpecies ON Animals (SpeciesID); The syntax is very similar to CREATE TABLE in that you say CREATE INDEX followed by an index name. The IX_ is a prefix that is sometimes conventionally used to name indexes. Next, we have the table name, and then we have something to describe the column. That way if you see IX_AnimalsSpecies, you automatically know it is talking about an Index for the Animals table that is on the column dealing with the species. To get rid of this index, use this: DROP INDEX IX_AnimalsSpecies; Which is also very similar to how we drop a table. Now, you can also create an INDEX on multiple columns if you are going to use them together very often. The way you do that is just add another column after a comma inside of the () in the index columns. For example: CREATE INDEX IX_AnmialsContact ON Animals (Name, ContactEmail); One other thing I wanted to show you is that you can actually create a UNIQUE Index by adding the keyword in: CREATE UNIQUE INDEX IX_Species ON Species (Species); This should only be used if every value for a column is labeled unique…In fact, it forces every row to be unique. Remember though that if a column is labeled UNIQUE, it is indexed by default...So this is not needed in this situation. In the next video we will discuss in more detail whether you want to use the UNIQUE Constraint create a UNIQUE index. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 8667 Caleb Curry
mysql tutorial for beginners (6/8) : Indexes As things stand, the table students works and can be searched without problem by MySQL—until it grows to more than a couple of hundred rows, At that point, database accesses will get slower and slower with every new row added, because MySQL has to search through every row whenever a query is issued. This is like searching through every book in a library whenever you need to look something up. Of course, you don’t have to search libraries that way, because they have either a card index system or, most likely, a database of their own. The way to achieve fast searches is to add an index, either when creating a table or at any time afterward. But the decision is not so simple. For example, there are different index types such as a regular INDEX, PRIMARY KEY, and FULLTEXT. Also, you must decide which columns require an index, a judgment that requires you to predict whether you will be searching any of the data in that column. And even when you’ve decided that, you still have the option of reducing index size by limiting the amount of each column to be indexed. If we imagine the searches that may be made on the students table, it becomes apparent that all of the columns may need to be searched. Anyway, go ahead and add an index to each of the columns, using the commands: ALTER TABLE students ADD INDEX(name(3)); An alternative to using ALTER TABLE to add an index is to use the CREATE INDEX command. They are equivalent, except that CREATE INDEX cannot be used for creating a PRIMARY KEY CREATE INDEX surname ON students (surname(5)); DESCRIBE students; These commands create indexes on both the name and surname columns, limiting name index to only the first 3 characters, and surname index to the first 5 characters. For instance, when MySQL indexes the following name: SAFAA It will actually store in the index only the first 3 characters: SAF This is done to minimize the size of the index, and to optimize database access speed. DESCRIBE command shows the key MUL for each column. This key means that multiple occurrences of a value may occur within that column, which is exactly what we want, as name or surname may appear many times. You don’t have to wait, until after creating a table to add indexes. In fact, doing so can be time-consuming, as adding an index to a large table can take a very long time. Therefore, let’s look at a command that creates the table students with indexes already in place. CREATE TABLE students ( Id_studnet SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(10) NOT NULL, surname VARCHAR(20) NOT NULL, email VARCHAR(30) NOT NULL, INDEX(name(3)), INDEX(surname(5)), ,PRIMARY KEY(id_studnet),UNIQUE(email)); Another important index, PK, its single unique key for each student to enable instant accessing of a row. The importance of having a key with a unique value for each row will come up when we start to combine data from different tables. You can add PK, while you create the table at the first time, or later by issuing the following command: ALTER TABLE students ADD PRIMARY KEY(id_student); The last important index, FULLTEXT index Unlike a regular index, MySQL’s FULLTEXT allows super-fast searches of entire columns of text. It stores every word in every data string in a special index that you can search using “natural language,” in a similar manner to using a search engine. It’s not strictly true that MySQL stores all the words in a FULLTEXT index, because it has a built-in list of more than 500 words that it chooses to ignore because they are so common that they aren’t very helpful for searching anyway. This list, called stopwords, includes the, as, is, of, and so on. The list helps MySQL run much more quickly when performing a FULLTEXT search and keeps database sizes down. FULLTEXT indexes can be created for CHAR, VARCHAR, and TEXT columns only. A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE (or CREATE INDEX). Adding a FULLTEXT index to the table students for the columns name and surname ALTER TABLE classics ADD FULLTEXT(name,surname); this index is in addition to the ones already created and does not affect them You can now perform FULLTEXT searches across this pair of columns. If you find that MySQL is running slower than you think it should be when accessing your database, the problem is usually related to your indexes. Either you don’t have an index where you need one, or the indexes are not optimally designed. Tweaking a table’s indexes will often solve such a problem. In the next tutorial, we will learn about, using FOREIGN KEY Constraints and how to join tables together. Subscribe for more: ---------------------------------------------------------------------------- https://www.youtube.com/subscription_center?add_user=saf3al2a SWE.Safaa Al-Hayali - saf3al2a
Views: 28193 Safaa Al-Hayali
Hi Friends, In this in this session we are going to learn the differences between primary key and unique key This is one of the most frequently asked sql server interview questions. Before we discuss the difference between primary key and unique key, let's see what a primary key is and what a unique key is. Primary Key: Primary key is a key which uniquely identifies each row/record in the table. This implies if you have a primary key on a table, you can identify each and every row in that table uniquely using primary key. Unique Key: Unique key constraint enforces the uniqueness on the columns in which it is defined. That is Unique key will make sure only unique values are allowed in the columns in which it is defined. Similarities: Both Primary key and unique Key enforces uniqueness on the columns on which they are defined. • Primary key and unique Key columns allow only unique values. • Primary key and unique Key columns will avoid duplicates • Both Primary key and unique Key columns uniquely identifies each row/record in the table Differences: PRIMARY KEY UNIQUE KEY Primary Key Does not allow duplicates Unique key allows only one NULL A table can have only 1 Primary Key A table can have multiple Unique Keys By default Primary Key creates a Unique Clustered Index on the table By default Unique Key creates a Unique Non-Clustered Index on the table. I hope this session is clear and i hope this knowldge should help you in interviews. Please share your valuable comments & sugesstions. Also, subscriber to my channel for more sql server interview questions and answers. Bye bye & Have a good day.
Views: 40157 TheSSScreations
Dr. Soper shows how to create simple and composite primary keys and foreign key relationships in a SQL Server database using SQL Server Management Studio. Several different methods of creating keys and establishing relationships between tables are demonstrated.
Views: 415177 Dr. Daniel Soper
Explaining the basic constructs of a relational database: Tables, Primary Keys, Foreign Keys, and Relationships. The database is normalized.
Views: 353327 minderchen
Refer Complete Tutorial : http://haneefputtur.com/create-manage-oracle-database-using-oracle-sql-developer-gui.html Create Oracle Database Tables Using GUI SQL developer Tools Tasks: • Create Orale tables using Oracle SQL Developer Creating Oracle Tables . 1. Oracle database required: DNS Name : xxxxxxxxx User Name: haneef Password : xxxxxxxxxxxxxxx Service Name : Haneef 2. Software Required : Oracle SQL Developer ( this requires JDK ) Link to Download : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html Create two tables. 1. Employee 2. Department Employee Table : • Employee ID ( Primary key) • Employee Name • Employee Department • Employee Salary Department Table: • Department ID (Primary key) • Department Name • Department Head Create Foreign Key For Employee Department to get ID from Department Table. Create DDL and Execute them.
Views: 27302 Mahammad Haneef
Database Tutorial. This video is about Database Fundamentals. I hope this series of videos can help those who want to be Database Professionals. I will cover various database technologies including Oracle Database, Microsoft SQL Server database and Sybase. Video: What is the Difference between Primary Key and Unique Key (Video 14 in the Database Tutorial Series) (Common for Oracle/Microsft SQL Server/Sybase /MySQL)
Views: 34706 Sam Dhanasekaran
Candidate key definition and understanding with the help of examples. Happy Learning and Keep rising.
Views: 77421 Edu Therapy
SQL Server / TSQL Tutorial Scenario: You are working as SQL Server developer, you need to create a table dbo.Customer with composite primary key by using columns FName and SSN. One you are done with creating primary key in dbo.Customer table, you need to create second table dbo.Orders and create foreign key constraint by using Primary Key columns. Link to scripts used in SQL Server / TSQL Tutorial Video http://www.techbrothersit.com/2016/04/how-to-create-foreign-key-constraint-on.html Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 4964 TechBrothersIT
http://technotip.com/2931/primary-key-foreign-key-implementation-mysql/ Implementation of Primary Key and Foreign Key, using MySQL Practical approach and the benefits of using Primary and Foreign key concept in a relational database design.
Views: 236220 Satish B
In this video you will learn about Primary Key and Foreign Key Constraints in PL/SQL Oracle. For Support =========== Email: [email protected] Contact Form: http://www.learninhindi.com/home/contact Our Social Media ================ Facebook: https://www.facebook.com/LearnInHindi Twitter: https://twitter.com/LearnInHindi For Training & Videos ===================== For more videos and articles visit: http://www.learninhindi.com Free Java Programming In Hindi Course ===================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH Free Oracle PL/SQL Programming In Hindi Course ============================================== https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6 Free C Programming In Hindi Course ================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq Trips & Tricks Channel ====================== https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw Programming in Hindi Channel ============================ https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 10866 ITORIAN
An interesting thing you can do with primary keys is that you can make a combination of columns the primary key. This is important when more than one column is required to make something unique. In an intermediary table, instead of having an association ID, we can have the combination of two rows as the primary key. This is known as either a compound or composite primary key. In the situation of using natural keys, composite keys are more common. For example, you could have a shopping website that allows multiple people from the same household to use a certain coupon you sent out. In this situation, you could use the address and the person's name as the natural primary key. What is the difference between a primary key and a column that has UNIQUE and NOT NULL constraints? The primary difference has to do with indexing. When you create a primary key, the column will automatically be indexed. This means that working with this data is faster. Now, we haven't talked a ton on indexes, but by default this will create a clustered index. UNIQUE constraints are also indexed by default, but the default index for a UNIQUE constraint is a non-clustered index. Clustered indexes determine how the table is actually stored, while non clustered indexes will make a sorted list that has reference to the data. This is not a life changing difference because these can actually be changed. We will discuss all of the details of indexing in future videos. As a side note, the IDENTITY column is not automatically indexed. How do we reference primary keys? This requires what is known as a foreign key, which we will discuss in the next video. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 10576 Caleb Curry
** For Online Training Registration: https://goo.gl/r6kJbB ? Call: +91-8179191999 💡 Visit Our Website for Classroom Training: https://nareshit.in/sql-server-training/ 💡For Online Training: https://nareshit.com/course/sql-server-online-training/ #sqlserver #server #programming #course #Tutorials #Training #Videos -------------------------- -------------------------- 💡 About NareshIT: "Naresh IT is having 14+ years of experience in software training industry and the best Software Training Institute for online training, classroom training, weekend training, corporate training of Hadoop, Salesforce, AWS, DevOps, Spark, Data Science, Python, Tableau, RPA , Java, C#.NET, ASP.NET, Oracle, Testing Tools, Silver light, Linq, SQL Server, Selenium, Android, iPhone, C Language, C++, PHP and Digital Marketing in USA, Hyderabad, Chennai and Vijayawada, Bangalore India which provides online training across all the locations -------------------------- 💡 Our Online Training Features: 🎈 Training with Real-Time Experts 🎈 Industry Specific Scenario’s 🎈 Flexible Timings 🎈 Soft Copy of Material 🎈 Share Videos of each and every session. -------------------------- 💡 Please write back to us at 📧 [email protected]/ 📧 [email protected] or Call us at the USA: ☎+1404-232-9879 or India: ☎ +918179191999 -------------------------- 💡 Check The Below Links ► For Course Reg: https://goo.gl/r6kJbB ► Subscribe to Our Channel: https://goo.gl/q9ozyG ► Circle us on G+: https://plus.google.com/+NareshIT ► Like us on Facebook: https://www.facebook.com/NareshIT ► Follow us on Twitter: https://twitter.com/nareshitek ► Follow us on Linkedin: https://goo.gl/CRBZ5F ► Follow us on Instagram: https://goo.gl/3UXYK3
Views: 62688 Naresh i Technologies
The terms covered in this video are: SQL RDBMS Relations NULL Database design Relationships Primary key Foreign key Constraint Indexes Data type HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://Twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 64401 Caleb Curry
Indexes allow us to tell the database that certain columns are columns we want to retrieve commonly. This allows the database to optimize retrieval. Additionally, when we get into the SELECT statement, we will often have to join data from multiple tables. When we correctly add indexes to our tables, we can improve the speed of our SELECT and also our joins. There are two types of Index. One is called clustered and the other is called non-clustered. Clustered indexes determine the actual order of the table. By default this is going to be the primary key column. So when you label a column as PRIMARY KEY, you are also going to be creating an INDEX that is a clustered index. A clustered index is kind of like a telephone book, where the data is right there when you look up a phone number. This means that if you are just making a small database that is only going to store a few things, you may be fine. But more likely than not you are going to want to add additional indexes. When you add a new index, you create a non-clustered index. These things do not actually determine the order of the rows in the table. A way of how you can think of how these work is like an index in the back of a book. The index of the book does not actually contain the data, it just tells you where to find it. What columns do you index? You are going to want every primary key to be indexed. What about foreign keys, are they indexed by default? No, they are not. Of all columns, you are probably going to want to index the foreign keys the most because they are used in joins. Lastly, you will likely want to index columns that you are going to use on a regular basis. You can also make composite indexes just like you can make composite keys. You would want to do this when you are going to constantly being using those columns together. There is a lot to learn on multicolumn indexes. We may explore this concept in more detail and see how SQL Server uses them in a future video, but as of right now it's probably more important that we get a general understanding of all things SQL Server before we deep dive on something like that. In the upcoming videos I'll be teaching you how to create indexes. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 10133 Caleb Curry
SQL is a standard Structured query language for storing, manipulating and retrieving data in databases. NOT NULL , UNIQUE , PRIMARY KEY , FOREIGN KEY , CHECK , INDEX , default 1) NOT NULL - Ensures that a column cannot have a NULL value 2) UNIQUE - Ensures that all values in a column are different 3) PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table 4) FOREIGN KEY - Uniquely identifies a row/record in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. prevents invalid data and destroy links between tables. 5) CHECK - Ensures that all values in a column satisfies a specific condition 6) DEFAULT - Sets a default value for a column when no value is specified 7) INDEX - Used to create and retrieve data from the database very quickly.
Views: 30 R K
Oracle - Managing Views, Sequences, Synonyms, and Index
Views: 250 Si Ruy
In this video I have shown an demo how to create foreign key relationship between two tables (customers and orders table) using migrations.
Views: 4074 Ruby
This video contains the basic concepts of Clustering Index and will help students in various competitive exams like GATE , NET, PSU'S etc Following are the important topics of dbms ER‐model - entity–relationship model, strong entity set, weak entity set, single valued attribute, multivalued attribute, stored attribute, derived attribute, simple attribute composite attribute, weak relationship, strong relationship, mapping, cardinality ratios, discriminator attribute, fan trap, chasm trap Relational model – relational table, column, domain, row, tuple, relational algebra – selection, projection, union, intersection, set difference, Cartesian product, natural join, left outer join, right outer join, complete outer join, theta join, division operator, nested query, safe query tuple calculus – tuple relational calculus, domain relational calculus, SQL – select, from, where, order by, group by, max, min, avg, count, sum, having, Integrity constraints – super key, candidate key, primary key, foreign key, alternate key, secondary key, surrogate key normal forms – first normal form, second normal form, third normal form, bcnf, 4nf, 5nf, functional dependency, minimal cover, canonical collection, multivalued functional dependency, dependency preserving, lossy and lossless decomposition. File organization – indexing, B, B+ trees, key attribute, anchor attribute, primary indexing, secondary indexing, clustered indexing, multilevel indexing, block pointer, tree pointer, record pointer, top down search, sequential search, range query, index file, ordering, non-ordering Transactions and concurrency control- transaction, acid properties, atomicity, consistency, isolation, durability, life cycle of a transaction, active state, partially committed state, committed state, abort, rollback, terminated, phantom read, dirty read, unrepeatable read, lost update problem, conflict serializability, view serializability, irrecoverable schedule, cascading rollback, recoverable schedule, cascadless schedule. Lock based protocol, two phase locking, exclusive lock, shared lock, growing phase, shrinking phase, conservative two-phase locking, rigorous two phase locking, strict two phase locking, time stamping, time stamp, read, write, deadlock, granularity, tree based protocol,
Views: 75474 KNOWLEDGE GATE
You Can Find the full course of 3 hours in a given link with 50% Coupon= https://goo.gl/g8SyVQ Learn SQL and Database Core Concepts: This SQL Tutorial will cover all Major SQL concepts from Scratch. SQL developers are earning higher salary in IT industry, but, its not about writing queries its about understanding and applying the right query at right time and this course will let you understand complex SQL Statements in an easy way . Moreover, This Course will teach you how to extract Data from Database and write complex queries to a database This course will focus on a wider scale by Covering Structure Query Language SQL concepts as a whole, whether Students work with MySQL, Microsoft SQL Server, Oracle Server, etc. Enroll Today! Lets start together. Jazeb Akram All Right Reserved https://www.Jazebakram.com https://twitter.com/jazebakram https://www.udemy.com/user/jazeb-akram/ https://goo.gl/g8SyVQ Coupon Code = 1YOUTUBE0
Views: 6511 Jazeb Akram
Curso Oracle SQL Essentials http://bit.ly/1N3upVn Curso Oracle SQL Expert - Certificação 1Z0-047 http://bit.ly/1G4rKWG Curso Oracle Aprendendo SQL - Preparatório exames 1Z0-051/1Z0-061 http://bit.ly/1eqDlsr Neste Curso de Oracle 10g será demonstrado todo passo a passo desde a istalação até administração e programação em PLSql. No Curso de Oracle 10g você será direcionado passo a passo durante o aprendizado com ótima qualidade do conteúdo disponibilizado. Este curso é oferecido gratuitamente aqui porém caso tenha interesse em se aprofundar e aprender o oracle a fundo praparando para certificação acesse os links acima. https://youtu.be/tcjwZYKDKW4 Tags: oracle cursos gratuitos curso de oracle banco de dados
Views: 3558 Cursos Interativos
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/09/part-12-can-we-join-two-tables-without.html Can we join two tables without primary foreign key relation Yes, we can join two tables without primary foreign key relation as long as the column values involved in the join can be converted to one type. ID column in Departments table is not the primary Key and DepartmentId column in Employees table is not the foreign key. But we can still join these tables using ID column from Departments table and DepartmentId column from Employees table, as both the columns involved in the join have same data type i.e int. Select Employees.Name as EmployeeName, Departments.Name as DepartmentName from Employees join Departments on Departments.ID = Employees.DepartmentId The obious next question is, if primary foreign key relation is not mandatory for 2 tables to be joined then what is the use of these keys? Primary key enforces uniqueness of values over one or more columns. Since ID is not a primary key in Departments table, 2 or more departments may end up having same ID value, which makes it impossible to distinguish between them based on the ID column value. Foreign key enforces referential integrity. Without foreign key constraint on DepartmentId column in Employees table, it is possible to insert a row into Employees table with a value for DepartmentId column that does not exist in Departments table. The following insert statement, successfully inserts a new Employee into Employees table whose DepartmentId is 100. But we don't have a department with ID = 100 in Departments table. This means this employee row is an orphan row, and the referential integrity is lost as result Insert into Employees values (8, 'Mary', 'Female', 80000, 100) If we have had a foreign key constraint on DepartmentId column in Employees table, the following insert statement would have failed with the following error. Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database "Sample", table "dbo.Departments", column 'ID'.
Views: 116831 kudvenkat
Foreign Key in Oracle oracle foreign key WHAT IS A FOREIGN KEY IN ORACLE? 1)A foreign key is a use to make referential integrity in database. 2)Foreign key values in one table must also appear in another table also. 3)The referenced table is called the parent table. 4)Table with the foreign key is called the child table. 5)The foreign key generally reference a primary key in the parent table. CREATE TABLE DEPT --- parent table ( DEPT_ID NUMERIC(10) NOT NULL, EMP_ID NUMERIC(10) NOT NULL, CONSTRAINT DEP_PK PRIMARY KEY (DEPT_ID) ); CREATE TABLE EMPLOYEE -- child table ( EMP_ID NUMERIC(10) NOT NULL, NAME VARCHAR2(50) NOT NULL, CITY VARCHAR2(50), DEPT_NO NUMERIC(10), CONSTRAINT ID_PK PRIMARY KEY (EMP_ID), CONSTRAINT FK_DEP FOREIGN KEY (DEPT_NO) REFERENCES DEPT(DEPT_ID) ); #techquerypond https://techquerypond.wordpress.com https://twitter.com/techquerypond
Views: 442 Tech Query Pond
Tutorial kasar : -sqlplus oa/oa -connect as sysdba; -create user paijo identified by paijo; -grant all privilege to paijo; -connect paijo/paijo -show user //lalu buat file di oracle home dengan nama paijo.sql dengan isi: ========================================= DROP TABLE "DOKTERS"; DROP TABLE "PASIENS"; DROP TABLE "DIAGNOSAS"; CREATE TABLE DOKTERS ("ID_DTR" VARCHAR (7), "NAMA" VARCHAR (25) NOT NULL, "ALAMAT" VARCHAR (30) NOT NULL, CONSTRAINT "ID_DTR_PK" PRIMARY KEY ("ID_DTR") USING INDEX ENABLE); CREATE TABLE PASIENS ("ID_PSN" VARCHAR (9), "NAMA" VARCHAR (25) NOT NULL, "ALAMAT" VARCHAR (30), CONSTRAINT "ID_PSN_PK" PRIMARY KEY ("ID_PSN") USING INDEX ENABLE); CREATE TABLE DIAGNOSAS ("TANGGAL" DATE, "HASIL" VARCHAR (30) NOT NULL, "ID_DTR" VARCHAR (7), "ID_PSN" VARCHAR (9), CONSTRAINT "TGL_PK" PRIMARY KEY ("TANGGAL", "ID_DTR", "ID_PSN") USING INDEX ENABLE); ====================================== [email protected] -desc dokters; -desc pasiens; -desc diagnosas; //lalu buat file di oracle home dengan nama alter_paijo.sql dengan isi: ===================================== ALTER TABLE DIAGNOSAS ADD CONSTRAINT "DIA_ID_DTR" FOREIGN KEY ("ID_DTR") REFERENCES DOKTERS ("ID_DTR"); ALTER TABLE DIAGNOSAS ADD CONSTRAINT "DIA_ID_PSN" FOREIGN KEY ("ID_PSN") REFERENCES PASIENS ("ID_PSN"); =================================== [email protected]_paijo.sql //lalu buat file di oracle home dengan nama isi_paijo.sql dengan isi: =================================== INSERT INTO DOKTERS VALUES('D100799','PAIJO','JALAN IN AJA'); INSERT INTO DOKTERS VALUES('D120498','PAINO','JALAN JALAN'); INSERT INTO DOKTERS VALUES('D071297','PAIWO','JALAN YANG BENAR'); INSERT INTO PASIENS VALUES('PSN130193','JARWO','JALAN LURUS'); INSERT INTO PASIENS VALUES('PSN040494','JARVIS','JALAN GRAK'); INSERT INTO PASIENS VALUES('PSN121212','JARAN','JALAN BERBELOK'); INSERT INTO DIAGNOSAS VALUES(TO_DATE('18-12-18','DD-MM-YY'),'PARU KERING','D100799','PSN130193'); INSERT INTO DIAGNOSAS VALUES(TO_DATE('19-12-18','DD-MM -YY'),'KAGETAN','D120498','PSN040494'); INSERT INTO DIAGNOSAS VALUES(TO_DATE('20-12-18','DD-MM-YY'),'GATEL','D071297','PSN121212'); ================================ [email protected]_paijo.sql -select * from dokters; -select * from pasiens; -select * from diagnosas; //lalu buat file di oracle home dengan nama select_paijo.sql dengan isi: ============================== SELECT DOKTERS.ID_DTR, DOKTERS.NAMA, PASIENS.ID_PSN, PASIENS.NAMA, DIAGNOSAS.TANGGAL, DIAGNOSAS.HASIL FROM DOKTERS, PASIENS, DIAGNOSAS WHERE DOKTERS.ID_DTR = DIAGNOSAS.ID_DTR AND PASIENS.ID_PSN= DIAGNOSAS.ID_PSN =========================== [email protected]_paijo.sql
Views: 70 Dhymas Julyan Riyanto
Timestamps: 0:45 - Basics 5:03 - TX lock demo 9:30 - TM lock info 21:04 - Foreign key index demo 28:48 - 10g, 11g, 12c locking differences 41:50 - New online operations in 12c 46:26 - Event 10704 trace 50:34 - Deadlock demo 53:42 - Q&A Franck discusses different types of locking scenarios to consider with your systems. The slides are available at: Google: https://drive.google.com/folderview?id=0B0DLaAfeW6uKZmJ3TXE0WGtLYlE Dropbox: https://www.dropbox.com/sh/9koxoemak8ts7j6/AADozVVYLoK_FdDZbQGC6TYta This event was sponsored by Red Stack Technology (http://redstacktechnology.com/). See more events at http://OracleMidlands.com/ Copyright Disclaimer Under Section 107 of the Copyright Act 1976, allowance is made for fair use for purposes such as criticism, comment, news reporting, teaching, scholarship, and research. Fair use is a use permitted by copyright statute that might otherwise be infringing. Non-profit, educational or personal use tips the balance in favor of fair use. "Fair Dealing" under UK Copyright, Designs and Patents Act 1988.
Views: 787 Oracle Midlands
Hi Friends, Here we are learning about INDEX in Oracle. Simple Index, Composite Index and Unique Index is explained. Please let me know your feedback in comment box. Thanks! Happy Coding.
Views: 86 YourSmartCode
In the last video I mentioned that with our database design it is important to make sure that nobody tries to update a user's username. What happens if they do? Nothing horrible, Oracle will just throw an error. That's not such a big deal, but if you are hoping to make some kind of application that allows someone to change their username, this is not the best set up. Why? If you look at the projects table, we have a foreign key that references the username. Let's assume for a moment that Oracle allows you to do anything with your data. That means that if a user updates their username, there will be projects created by users that don't exist. Or a user could change their name to the previous owner. To fix this problem, we would need something such as an ON UPDATE CASCADE command for our foreign key. That would mean that if the user updated their username, the columns that reference that username would also update to the new value. This exists in some database management systems, but this does not exist in Oracle at the time of this video. How do we get around this problem? I'm sure we could conjure up something to allow us to update the username, but the easiest solution is to reference the user_id instead of the username. That way, when the username is updated, nothing changes inside of the foreign key. As a general rule, primary keys should never change. Foreign keys CAN change, but they should not change because a primary key changed. So, if we did happen to use a username as a column, it would be frowned upon if the username had to change because the column it references changes. However, it would be acceptable to change the foreign key if we needed to point to a new entity in the users table. Even if a username is never intended to change, these complications bother a lot of people. You can mitigate these problems by only referencing surrogate keys in foreign keys. This has the downside though that when you retrieve the data, you are going to have to do more work to make the data readable. For example, we had a table that was called project_users. It is essentially a table that says what users are part of what projects. We could have the foreign keys reference the project's name and the user's username. Then when you could say SELECT * FROM project_users. The data would be completely readable without doing anything. If you switch to only referencing surrogate primary keys, you will have a bunch of random numbers that don't mean anything and will have to be joined with other tables…which is really super frustrating when later you have to join a thousand tables to read anything. Which side do you prefer? Pick a side. Choose wisely. I'll see you all in the next video ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 5845 Caleb Curry
www.facebook.com/oracle.shohag Email : [email protected] Website : www.oraclebangla.com Topics : Constraint Foreign Key
Views: 1098 Oracle Bangla
Database Tutorial. This video is about Database Fundamentals. I hope this series of videos can help those who want to be Database Professionals. I will cover various database technologies including Oracle Database, Microsoft SQL Server database and Sybase. Video: What is a Foreign Key (Video 10 in the Database Tutorial Series) (Common for Oracle/Microsft SQL Server/Sybase)
Views: 7631 Sam Dhanasekaran
This tutorial is all about Sequence In Oracle Database. In this SQL Tutorial you will learn How to create a sequence How to populate a Primary key column (auto increment in Oracle) using Sequence How to drop a sequence ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/sql-sequence-in-oracle-database-rebellionrider-manish-sharma Previous Tutorial ► How To Insert Data into Table using SQL Developer http://youtu.be/YYQCSV6MzTk ► INSERT INTO Command http://youtu.be/uQXgqFtxI_k ► How to copy /Insert data into a table from another table http://youtu.be/m3Ep8tAMqok ------------------------------------------------------------------------- ►►►Help Me In Getting A Job◄◄◄ ►Help Me In Getting A Good Job By Connecting With Me on My LinkedIn and Endorsing My Skills. All My Contact Info is Down Below. You Can Also Refer Me To Your Company Thanks ------------------------------------------------------------------------- Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=kb4rc1 -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! -------------------------------------------------------------------------- Amazon Wishlist: http://bit.ly/wishlist-amazon ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 43359 Manish Sharma
http://sqlserver2008tutorial.com/ In this video training, we compare SQL indexes -- clustered index vs. non clustered index. In SQL server you can have one clustered index whereas you can have many non clustered index is. Using SQL Server Profiler and MS SQL Execution plans, we compare the clustered index and the nonclustered index. In this demo we show you how to create a clustered and non-clustered index using SSMS. Using different parameters like CPU, Number of Page Reads and Duration in msec, we come to a conclusion that the best option is to use a clustered index. Finally we go over index management and index fragmentation. We illustrate ALTER TABLE commands with Index Rebuild or Index Reorg. Other topics that we cover include a table scan; clustered index scan and an index seek.
Views: 50801 baghul